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]

Try

=H2-G2

If the time spans over midnight...
11:30 PM to 1:15 AM

=MOD(H2-G2,1)

 

[deafpride]

I did try H2-G2.

H2 and G2 have the dates in the cells, so the result I got was: 1/1/04 12:02 AM

Which means it's again messed up because of the dates I have in H2 and G2 before the time log.

 

[Jonmo1]

So you want the difference in time regardless of the dates?

1/1/2000 5:00:00 AM and 5/15/2011 11:05:00 AM
The result you want is 6:05:00


Try the other formula I posted using MOD

 

[deafpride]

That's correct, I just need the time log and I'll affiliate it with the dates through pivottable.

I tried the other option, too. Same result.

Thanks for your help and I'm sorry for the delay of response as I had other responsibilities.

 

[Scott Huish]

Excel Workbook
	A	B	C	D
1	1/1/2000 5:00	5/15/2011 11:05		6:05
Sheet1

Format result cell as h:mm

 

[deafpride]

Wow, it looks like once AGAIN the issue was formatting the cell correctly. It worked, THANK YOU!

How can I give you both a positive response?