Calculating hours worked based on elapsed time over 24H

dpbarry

Board Regular
Joined
Feb 10, 2003
Messages
214
Office Version
  1. 2016
Platform
  1. Windows
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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Declan

Why not include the date?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Declan

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

=(B1-A1)*24
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Declan

I'm kind of baffled as well.:o

Why do you have a less than operator in there?

Or is that a typo.:)
 
Upvote 0

Forum statistics

Threads
1,222,403
Messages
6,165,852
Members
451,986
Latest member
ExcelIsLove

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
Back
Top