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

 

[dpbarry]

Hi Norie..

I'm not sue why it is there. It a formula I got from the web that was(is) supposed to also help out in calculating the time correctly. It works fine if I use it in a normal S/sheet but can't seem to get it to work inside my macro

I've also tried barry houdini's combination but couldn't get it to work either.

Regards

Declan

 

[Norie]

Declan

I'm no formula guru so I don't know how that formula would work in a spreadsheet but I'm pretty sure you can't translate it directly into VBA, if that's what you've done.

Is there no way you can actually have the date and time in the same cell?

Seems to me if you could it might make things a little easier.

 

[cblincoln43]

Norie, I hope you can help him with this one. I have ran into it offten and the only way I have found around it is to manually enter the end time into another cell. Most of mine are from the answers I receive from formulas (results) for Indexing and Matching.
db, this one is out of my world (V.B.A.) I know nothing about how to even write one. Sorry (I sure will be watching this one.)

 

[cblincoln43]

would you repost your example only click yes when it ask you if you want the formula to show. I would like to see what formula is in J8.
Because below is what i keep getting. I have had a similar problem that was because of the formula in the cells the time is not manualy. I could only rework the form to manualy enter the time. ?????????

Book1
	A	B	C	D	E	F
3
4
5	1/7/2007	23:30	1/8/2007	4:00		4:30
6
7	1/7/07 23:30		1/8/2007 4:00			4:30
8
Sheet1

 

[dpbarry]

Hi Guys..

Sorry for the delay in getting back on this one. We had an Audit by government inspectors so a lot of paper shuffling had to be done as myself and 2 other people only took over the reins 6 months ago..

Okay, to the SS.

I’ve placed a copy of the SS at the link below and altered times to highlight my problem:

http://www.barryweb.co.uk/downloads/ss.xls

When you open up the SS, there are a number of worksheets. Setup, attendance and personnel are the main sheets and the others starting with Aboyd are the guys paysheets for the month which are populated from information on the attendance worksheet.

The Attendance worksheet is like a register (which can be cleared on one go on the setup sheet to start a new month.) All I do is go to the attendance sheet and mark each attendance by an officer with a ‘Y’. (defaults to ‘N’ when cleared from the setup worksheet).

Once all is done for the month, I then go back to the setup worksheet and click on ‘Generate CG48’s for Team’. This then runs the ‘populate’ macro which transfers the data from the attendance worksheet into the appropriate officers worksheet.

The transfer of the data work fine and in most cases, calculating the hours worked is okay but not if a time runs over from one day to the next. i.e 10pm on Friday to 2am on Saturday (22:00hrs Friday to 02:00hrs Saturday)

If you look at worksheet ‘Dbarry’ you will see that in the ‘Casualty’ column, I have 3 entries – 7, 12 and 15th of July. The 7th and 12th of July are showing the wrong hours worked. The 7th should show 6.0 hours and the 12th should show 5.0 hours (if I counted correctly). The 15th is actually correct because, if an officer responds to a Casualty and completion of the incident takes less than 3 hours, the officer get allocated a minimum of 3 hours worked. Bit of an incentive to cover costs etc.

The major problem is where a time runs over from one day to the next.

I hope someone can help with this one and/or make suggestion as to how to get around it.

In the activity column, CAS = Casualty, TRG = Training, C&M = Care and Maintenance, PR = PR & RSD = Routine Station Duties. These code are need so that the hours worked are put into the relevant columns in each officers worksheet

Regards

Declan

 

[CyberGuy2004]

Hi, Declan, here's a possibility: for your code 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

substitute these lines:

dim deltaDays as date
deltaDays = wks.Cells(c, 3) - wks.Cells(c, 2)  '  "raw" difference in days (Excel's internal date coding)
If deltaDays < 0 Then deltaDays = deltaDays + 1 '  if negative, add one day
s = deltaDays * 24 '  convert to hours

HTH

(edited to fix syntax problem in VBA code, and to delete a duplicate post)

 

[dpbarry]

Yippee

Many thanks CyberGuy2004

So far, That seems to have done the trick. I've tested it with a few scenarios and it seem to work.

Whats the difference between the way you have coded it and the way I had it? Is it the way that Excel interprets code in a cell as opposed to VBA. I think it stumped a few of us as the original code seemed to work fine if directly 'plugged' into a cell.

Once again, Many thanks

Declan

 

[CyberGuy2004]

I'm not 100% sure, but I think that TRUE and FALSE behave differently in formulas depending whether it's a cell formula or a VBA formula. (Norie said something about it a few posts back.) At any rate, I mistrust structures like "... + (A < B)... ". I can never remember whether TRUE is 1 or 0 or -1 or ...

 

[dpbarry]

Well, it seems to be doing the trick at present.

I think a few other people on the list were interested in an outcome for this type of scenario. I know one of the quys at another station uses the SS and has found it extremely helpful especially as all he has to do is get the other guys to sign a blank CG48's for the full year so all he needs to do is pop the relevant month into the printer and hey presto, 'End of Month' returns completed in a matter of minutes - signed and all!!

Cheers

Declan