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

[SpielbergRules]

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.

 

[phantom1975]

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

Would this work for you?

Book1
	A	B	C	D	E	F	G	H	I
2		SUN	MON	TUE	WED	THU	FRI	SAT
3	IN	8:00	0:00
4	OUT	0:00	9:15
5
6		16:00	9:15	0:00	0:00	0:00	0:00	0:00	25:15
Sheet1

 

[Ekim]

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
	A	B	C	D
1	Start	Finished	Hours
2	1-Feb-20048:00AM	2-Feb-20049:00AM	25:00
3	2-Feb-200411:00AM	2-Feb-20042:00PM	3:00
4
Sheet1

Regards,

Mike

 

[Royal Presence]

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

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

 

[SpielbergRules]

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!

 

[PaddyD]

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]

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.