Sleeper: Doing Time.....

[securityman]

I'm back again, still working with time.

Monday
in 8:00
out 17:00

Tuesday
in 6:00
out 17:00

I want to calculate the time between the clock out time on Monday and the clock in time on Tuesday.
_________________________________________


Monday
in 8:00
out 17:00

Tuesday
in 0:00
out 0:00

Wednesday
in 8:00
out 17:00

Want to calculate the number of hours between clock out time on Monday and the clock in time on Wednesday.

Then what if you have 2 day off between Monday and Thursday?

I can do a formula for the first one (Mon to Tues), but if I try for the second one, I have to do 6 IF statements. Surely theres a better way.

I have a sheet that will take times off a time card and calculate the hours worked for each day. Then I want it to do the above in a cell between each day to show how many hours they were off between shifts. (Sheet 1)

I then coded a button to put the hours worked for each day into another sheet (Sheet 2). That was no problem. On this sheet it takes the last entry in the column and adds the preveious 7 entries to it and then subtracts it from 70.
That's the number of hours worked in the last 8 days minus 70. This leaves me with how many hour the person can work the next day.

When the person has 34 hours or more off calculated on Sheet 1, I need the hours to be reset to 70 on Sheet 2 and the time to start subtracting from there.

EX: last column in sheet 2 is 10. (10 hours left to work the next day)
If the person has 34 or more hours off from Sheet 1 then the blank cell under the 10 is reset to 70 and the calculation start over. It always has to add the present cell plus the previous seven cell and subtract it from 70 unless a cell has been reset to 70 then it has to start subtracting from there.

Clear as mud?

By the way if any one recognizes this it is for truck drivers who work a 70 hour / 8 day Rule.

Right now Sheet 2 is calculating the hours correctly until someone takes 34 or more hours off. It won't reset to 70. I have it highlight the cell in which the 70 hours have been used up and needs to be reset, but you have to look back to Sheet 1 to calculate when (or if) someone took 34 or more hours off.

The workbook has 13 sheets in it. 12 for the months and 1 for the calculation of the hours worked per day. I could e-mail a copy to anyone willing to try and help me so they could see what I have so far.

Thanks,

[xCav8r]

Here's the difference in hours for the first question.


Function GetHoursDifference() As Integer
Const Date1 As Date = #5/10/2005 5:00:00 PM#
Const Date2 As Date = #5/11/2005 8:00:00 AM#
GetHoursDifference = DateDiff("h", Date1, Date2)
Debug.Print GetHoursDifference
End Function

Result is 15.

[securityman]

Thanx xCav8r.....

Is there anyway to do it without having a constant. I need it to read the time in the cells, place the results in a different cell, and then go to the next 2 time cells, etc...etc...

[xCav8r]

Sure...




Function GetHoursDifference() As Integer
Dim Date1 As Date
Dim Date2 As Date
Date1 = #5/10/2005 5:00:00 PM# 'refer this to cell value
Date2 = #5/11/2005 8:00:00 AM# 'refer this to cell value
GetHoursDifference = DateDiff("h", Date1, Date2)
Debug.Print GetHoursDifference
End Function

[geekgirlau]

What you might want to do is modify xCav8r's function so that you pass the two dates to the function. Then you can use the function in the same way as a normal formula.




Function GetHoursDifference(dtmStart As Date,dtmEnd As Date) As Integer
GetHoursDifference = DateDiff("h", Date1, Date2)
End Function


Your formula to calculate the hours is


=GetHoursDifference(A2,B2)

assuming that the start date/time is in cell A2, and the end date/time is in cell B2.

[securityman]

Thanks, geekgirlau! Looks like what I need.

Thanks everyone!!!

[securityman]

OK....I tried geekgirlau's code but hot a NAME error.

It doesn't recognize the function.

Is there a specific place I need to put it?

My endtime for say monday is in c16.
My starttime for tuesday is in c18.

so this is what I put in another cell ---- =GetHoursDifference(C18,C16)

When I hit ENTER it gives me #NAME? in that cell.

[geekgirlau]

Ugh, I must have hit that "Post Reply" button a bit too fast! :doh:

Should be



Function GetHoursDifference(dtmStart As Date, dtmEnd As Date) As Integer
GetHoursDifference = DateDiff("h", dtmStart, dtmEnd)
End Function

[securityman]

Still didn't work. Still got #NAME?.

I noticed that DateDiff was spelt wrong. Should be DateDif (one F )?

Also doesn't this function only return day, months or years?

When I spelt it correctly, it then gave me a NUM error.

Please correct me if I'm wrong.

[xCav8r]

See the attached. I think the function needs to be in a standard module to work.

[securityman]

Thanks everyone!!!

That did what I wanted, but.....actually it is too much trouble to have to put the date and the time in the cell for each time I have to transfer from a time card for this function to work. Unless I'm doing it wrong again.

I will save it to use else where if I happen to need it.

[xCav8r]

I guess I need more information about how you're doing things to be able to help you out there. You should be able to concatenate or add the dates and times using a simple formula, so I don't see why they would need to be re-entered, but I probably don't know enough about your situation to comment. ;)