Roundup time formula

[deafpride]

I'm trying to roundup my times by half hour, but I cannot get past the date issue.

I want the same date, but times are rounded by half hour.

4/12/2010 5:00:51 AM

 

[Jonmo1]

Welcome to the board...

Try

=CEILING(A1,"00:30:00"+0)
Cell with formula formatted as desired with date and time.


Hope that helps.

 

[Scott Huish]

What if the time was

4/12/2010 11:53:00 PM

Rounding the time up would normally force a new date.

 

[deafpride]

Thanks for your rapid response and welcome!

the date and time are in H2: 4/12/2010 5:00:51 AM

Therefore, I typed =CEILING(H2,"00:30:00"+0)

The column should come up as 4/12/2010 5:30:00 AM, but it came up as 30:00.

Hopefully this makes sense! Thanks!

 

[deafpride]

hotpepper, you are correct. So how can I get the time change? I've changed the format so that the time is first, but it does not change the cell.

 

[Scott Huish]

Format result cell as m/d/yy h:mm:ss AM/PM

Excel Workbook
	H	I
2	4/12/2010 5:00:51 AM	4/12/10 5:30:00 AM
Sheet1

So, you'd want to keep the same day, just modifying the time?

 

[Jonmo1]

The only way I can duplicate that is if I format the cell as hh:mm

Make sure you're formatting the cell with the formula as

m/dd/yyyy h:mm:ss

 

[deafpride]

Thank you BOTH! I've done the formula so many times in the last two hours and it was because I did not format my cells correctly. Very frustrating as this is a simple task, but you two walked me through it again.

This is RESOLVED! Wow, why didn't I join Mr. Excel before????

THANK YOU!!

 

[Jonmo1]

This is RESOLVED! Wow, why didn't I join Mr. Excel before????
THANK YOU!!

Glad to help...

That's a good question.

 

[deafpride]

Now I have one more question...

How can I place the exact time log between G2 and H2 in I2?

4:58:41 AM 5:00:51 AM