Going CrAzY trying to Calculate 24 Hour Time! Please Help!

SpielbergRules

New Member
Joined
Feb 6, 2004
Messages
2
:oops:

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.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
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
 

Royal Presence

Board Regular
Joined
Feb 27, 2003
Messages
92
Re: Going CrAzY trying to Calculate 24 Hour Time! Please He

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

SpielbergRules

New Member
Joined
Feb 6, 2004
Messages
2

ADVERTISEMENT

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!

(y)
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
As an addition...

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
 

cblincoln43

Board Regular
Joined
Mar 12, 2002
Messages
206
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. o_O
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top