Calculating hours worked based on elapsed time over 24H

[dpbarry]

Hi Folks.. I thought I had all bases covered but after rechecking calculations in my Coastguard Rescue Worksheet system, I've discovered a problem and I can't seem to work it out.

If a Rescue Officer is called out at 23:00 and is back at 04:00, this should equate to 5 hours worked.

It seems that if my times are all on one side or the other of a 24 hour cycle, my calculation work fine but it it breaks across the 24 hour (as above, it doesn't work.

A2=04:00
A1=23:00

Using (A2-A1)*24 give me -19.00 hours

My SS macro has a line:

s = (wks.Cells(c, 3) - wks.Cells(c, 2)) * 24 'calculates the duration of time worked

Is there any way of getting excel to calculate an elapsed time in hours when the start and end times roll over from one day to the next?

Hope that makes sense.

Declan

 

[Norie]

Declan

Why not include the date?

 

[VoG]

Try

=MOD(A2-A1,1)

with the result cell formatted as Time.

 

[dpbarry]

Declan

Why not include the date?

Oops.. Sorry Norie.

The date the incident took place on was the 27/07/2007 at 23:00 and ended on the 28/07/2007 at 04:00.

This equates to 5 hours but I get -19

Declan

 

[barry houdini]

Hello declan,

did you try VoG II's suggestion above? That will give you the correct answer in time format, i.e. 5:00 for your example. If you want the answer in decimal format, i.e. 5 then multiply by 24, i.e.

=MOD(A2-A1,1)*24

or

=(A2-A1+(A1>A2))*24

 

[Norie]

Declan

Well when I enter those dates and times in A1 and B1 I get 5 with this formula.

=(B1-A1)*24

 

[cblincoln43]

Here a really old way I received form Aladin awile back.
try the formula in S15

new time sheet 2007.xls
	N	O	P	Q	R	S	T	U	V	W	X	Y
12						Hours			Total
13		Date		Start	Ending	Worked	Lunch	Misc.	Hours	Reg.	O.T.	D.T.
14		Mon.	Tue.	17:15	03:15	10.0
15	Time	2/5	2/6	5:15 PM	3:15 AM	10:00
16	Time Card			17.2	3.2	10.0	0.5		9.5	8.0	1.5	0.0
17
Sheet1

 

[dpbarry]

Hi Norie..

In my SS, I have 3 columns. Col1=Date, Col2=Time On, Col2=Time Off.

Under normal circumstances, my calculation works fine subtracting Col2 from Col1. Its just when a time off ran over into a 'New Day' that things got messed up.

I've used VoG II's method and it seems to be doing what I want it to do. I just don't understand what it's doing.

Many thanks for helping me out on this one.

Can I use this in the macro which is currently in the format:

s = (wks.Cells(c, 3) - wks.Cells(c, 2)) * 24 'calculates the duration of time worked

I've tried a few versions to fit in VoG II's metod but get syntax errors.

Declan

 

[dpbarry]

Hi cblincoln43 (et al)

I've included a screendump of my problem as below. The problematic cell is J8

In my macro code , I have the line:

s = (wks.Cells(c, 3) - wks.Cells(c, 2) + (wks.Cells(c, 3)< wks.Cells(c, 2))) * 24 'calculates the duration of time worked

Stepping through it, the calculated result for this cell is -43.50 but should be 4.50

I can't seem to figure out what I've got wrong. 'c' in the code line just refers to 8 which is the start of my data due to rows 1 - 7 being header information for the Paysheet.

I'm baffled

Declan

Test Update CG48.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
6	Date	Times		Voluntary		RoutineStation Duties		PR		Training		Casualty
7		From	To	Aux	SO	Aux	SO	Aux	SO	Aux	SO	Aux	SO
8	01/07/2007	23:30	04:00							-43.50
9	05/07/2007	19:00	23:00							4.00
10	26/07/2007	19:00	22:30							3.50
11	26/07/2007	20:30	23:30									3.00
Aboyd

 

[Norie]

Declan

I'm kind of baffled as well.

Why do you have a less than operator in there?

Or is that a typo.