Date and Time Formula problems

[JakeMann]

Hi All,

I'm having issues with how a formula is working.

For example,

A1 B1
10/06/2013 00:00:00

A1 is formatted as a date and B1 as a custom (hh:mm)

A2 has the following formula
=IF(HOUR(B2)=0,A2+1,A2)

B2 has
=B1+"01:00"

In C1 I need to combine cells A1 and B1 so the formula is simply A1+B1 and then formatted as dd/mm/yyyy hh:mm


When I drag the formula in A2 and B2 down that's fine, but the formula is C1 seems to work fine for the first 24 hours and then jumps forward by 2 days.

Not sure why. Any ideas?

I've attached a text sheet so you can see what I mean...

[daddylonglegs]

B25 shows 00:00 but the actual value is 24:00 because you added 1 hour to 23:00 from the previous cell. One way to make that work is to use this formula in B2 copied down

=ROUND(MOD(B1+"01:00",1),9)

[ARGK]

Hi
Not sure how you are copying down the formulae but if you retype the "00:00" in B25 or copy and paste B1 to B25 it will correct the issue you have.
Hope this helps.
Tony

[JakeMann]

Thanks both, very helpful