#### SpielbergRules

##### New Member

Hello all, So glad I found this incredible forum... Not a newbie to Excel, (been using it since 1990) - just can't figure out how to solve this problem!

On a simple time sheet, I am trying to subtract to time to get the hours, rouded to to the nearest quarter hour. The times entered are generally "pre-rounded" (i.e. entered as 9:15 am, 10:45 pm, etc.)

The problem comes in the type of business I am in often requires strange hours that can overlap a day.

For example, I may start at 8:00 am on Monday, and not finish until 9:00 am the following day.

What type of job would someone take where they have to work a 25 hour day? Glad you asked. I work in concert lighting and video production and believe me, that is a SHORT day!

Anyway, so as you can imagine, subtracting 9:00 am from 8:00 am will not give me the correct answer.

I tried searching the forum, the internet, my mind (which pretty much came up empty). THEN - I found this forum. SOMEWHERE on this forum, there is a guru that can figure this out.

My calculator and myself will be eternally grateful.

Thanks in advance to anyone who can help.

Mike

P.S. Sorry I can't get you tickets to a concert for helping. We don't do THOSE types of concerts, unfortunately.

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Re: Going CrAzY trying to Calculate 24 Hour Time! Please He

Would this work for you?
Book1
ABCDEFGHI
2SUNMONTUEWEDTHUFRISAT
3IN8:000:00
4OUT0:009:15
5
616:009:150:000:000:000:000:0025:15
Sheet1

Re: Going CrAzY trying to Calculate 24 Hour Time! Please He

If there is a chance that your times will span midnight, custom format the result cells, column C, as “[h]:mm” (no quotes).

The custom format in cells A2:B3 is “dd-mmm-yy hh:mm AM/PM” (no quotes).
Book7
ABCD
1StartFinishedHours
21-Feb-20048:00AM2-Feb-20049:00AM25:00
32-Feb-200411:00AM2-Feb-20042:00PM3:00
4
Sheet1

Regards,

Mike

Re: Going CrAzY trying to Calculate 24 Hour Time! Please He

Will military time help?
##":"##

Re: Going CrAzY trying to Calculate 24 Hour Time! Please He

Thanks for your tips guys I will definitely try those great tip out and let you know!

given that excel stores times as decimal fractions of days, it's always good idea to enter times with a day attached - as you have discovered!

In general, elapsed times that could span 24 hours can be calculated with a formula of the form:

=(end< start)+(end-start)

...although this will fall over if the total shift can be > 24 hours

Re: Going CrAzY trying to Calculate 24 Hour Time! Please He

Do a search here for cblincoln43, this time thing realy is crazy but the folks on the board gave me a lot of great information that might help with your head ache. Like = b1-a1+ (b1 < a1) for going past the midnight hour. Phantom 1975 posting is something new to me but it looks like it is something that will help me in the future to.
Thanks Phamtom.
Bob. cblincoln43, Still learning.

Replies
16
Views
389
Replies
13
Views
553
Replies
8
Views
357
Legacy 143009
L
Replies
8
Views
355
Replies
6
Views
763

1,211,803
Messages
6,104,071
Members
447,892
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back