Round total time to 1 hour multiples with a minimum of two

[2funny4words]

I need a basic formula that will take my start time from my end time and round it up to the next hour with a two hour minimum. I formated my cell to not have decimals and I tried to use round and roundup along with and if/then formula but no luck. If it makes any difference, all times are in 24hr format.

Thanks

[mewingkitty]

Please Login or Register  to view this content.

Something like that?

[JBeaucaire]

This accomplishes the task without the double evaluation of an IF, and it converts the real TIME data in A1 an B1 into an integer:

Please Login or Register  to view this content.

=MAX(2,(B1-A1)*24)

...result in a normal "2" in this instance. Make sure the results cell is formatted as Number.

[2funny4words]

This accomplishes the task without the double evaluation of an IF, and it converts the real TIME data in A1 an B1 into an integer:

Please Login or Register  to view this content.

=MAX(2,(B1-A1)*24)

...result in a normal "2" in this instance. Make sure the results cell is formatted as Number.

This is the one that I have tried so far since it is simpler, the only thing that I need to change is that I need to round the time up to the next hour even if I am just one minute into that hour.
Also, (guess this makes two things, but....) sometimes my start time is say 23:38 and my end time is 03:54. What I would like to make possible is that the end user (a firefighter after a fire at three A.M.) does not have to add up and input 27:54 (which does give me what I need and also shows up as 03:54) but just makes things more difficult and prone to error.

Thanks to both of you for your input

[JBeaucaire]

No way I'm going to try and figure that out. Tell you what, mock a spreadsheet with some fake data in it and show ALL the situations that you need to cover. Put in start and end times and the preferred resulting calculation for each sample. Just do the math manually and explain it where needed.

SHOW us what you're doing and all the variables therein, then we can help automate it.

[DonkeyOte]

You can use MOD to handle times that may/may not cross midnight:

=MAX(2,ROUNDUP(24*MOD(B1-A1,1),0))

[2funny4words]

Thanks, that works just like I needed it to!