Simple hour vs day formula

[sassriverrat]

Good Evening,

I have a formula here that calculates the time difference from one date to another. It should spit this back in whole hours and it does. However, if I go over 23 hours (i.e. noon to noon), it goes back to zero because I think it's taking out a day. I need it to be able to hit 24+ hours. How might I fix this?

Note:
D4 is a time (both the active and the previous sheet)
F4 is a date (both the active and the previous sheet)

 

[mole999]

have you formatted as time [h]:mm and NOT h:mm

 

[James006]

Hello,

You can use formula :

=Mode(b2-a2,1)

HTH

 

[sassriverrat]

have you formatted as time [h]:mm and NOT h:mm

No formatting- it's just "general"

 

[sassriverrat]

@James006

So I threw this together and it equals 1 if the period is 24hrs (1200 to 1200) but as soon as it should be 25hrs (1200-1300 the next day), it kicks out. Basically, I need this thing to give me an hour count between two dates/times.

=mode((HOUR(ABS((($D$4+$F$4)-((PrevSheet($D$4)+PrevSheet($F$4)))))),1)

 

[steve the fish]

You could do:

=ABS($D$4+$F$4-PrevSheet!$D$4-PrevSheet!$F$4)*24

 

[sassriverrat]

Hmmmm.... @steve the fish

makes perfect sense to me. I wonder why I would have had "Hour" in the front of that....I'm sure I had a reason but I can't think of it.
the adjacent cell has minutes in it so I basically have the same formula but with "minute(abs etc etc)" and it works.

 

[steve the fish]

You will have the same trouble with minutes if you want them to go over 60.

 

[sassriverrat]

No no minutes are only supposed to go up to 59
and then pop up the hour and reset the minutes to 00 and never hit 60

 

[mole999]

unless you format as TIME with square brackets around the hour you will not see greater than 23