# Calculating hours worked based on elapsed time over 24H

#### dpbarry

##### Board Regular
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

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### Norie

##### Well-known Member
Declan

Why not include the date?

#### VoG

##### Legend
Try

=MOD(A2-A1,1)

with the result cell formatted as Time.

#### dpbarry

##### Board Regular
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

##### MrExcel MVP
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

##### Well-known Member
Declan

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

=(B1-A1)*24

#### cblincoln43

##### Board Regular
try the formula in S15
new time sheet 2007.xls
NOPQRSTUVWXY
12HoursTotal
13DateStartEndingWorkedLunchMisc.HoursReg.O.T.D.T.
14Mon.Tue.17:1503:1510.0
15Time2/52/65:15 PM3:15 AM10:00
16Time Card17.23.210.00.59.58.01.50.0
17
Sheet1

#### dpbarry

##### Board Regular
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

##### Board Regular
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
ABCDEFGHIJKLM
6DateTimesVoluntaryRoutineStation DutiesPRTrainingCasualty
7FromToAuxSOAuxSOAuxSOAuxSOAuxSO
801/07/200723:3004:00-43.50
905/07/200719:0023:004.00
1026/07/200719:0022:303.50
1126/07/200720:3023:303.00
Aboyd

#### Norie

##### Well-known Member
Declan

I'm kind of baffled as well.

Why do you have a less than operator in there?

Or is that a typo.

Replies
3
Views
344
Replies
2
Views
101
Replies
21
Views
592
Replies
0
Views
460
Replies
41
Views
499

1,190,558
Messages
5,981,686
Members
439,729
Latest member
purna

### 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