Excel Help

Status
Not open for further replies.

azzkiker

Newbie
Joined
Feb 25, 2012
Messages
0
Tyring to create a simple log to track duty time in excel.

I need to separate/calculate time on duty, and time off duty.

Seems simple, but I cant figure out a way to account for days off.

I have attached an example, the times in the first two are correct... but the cell after two days off obviously shows only the time since the day began rather than the gross total of time off since the last shift ended.

How can I make this work?
 

Attachments

KD8PVX

Member
Joined
Feb 9, 2011
Messages
22
Location
Charlotte, MI
I changed the format of cell E:34 to time 00:00 and it came up with the proper hours...

Plus for your "Rest Hours" I would probably do =SUM(24-D#) Rather than trying to take the previous day's time clocked out minus the time clocked in that day.

I hope this is what you were looking for. Please let me know if this does not work for you and possibly a why it does not so I can help you figure it out.

- William
 

azzkiker

Newbie
Joined
Feb 25, 2012
Messages
0
Thanks for trying... The rest hours reported must be the difference between the last clock out, and the following clock in... not the number of hours not worked that day. Its probably a really complex thing considering there will be days off leading to blank cells. The days off have to be accounted for.
 

KD8PVX

Member
Joined
Feb 9, 2011
Messages
22
Location
Charlotte, MI
I do apologize for not replying sooner. This may work for you and please let me know if this does not. I would use this formula for Cell E4 then copy & past that cell to all the others in row column E below row 4. It will not work on E3, because there is no data it could go by on the previous row.

Code:
 =IF(C4=B4,IF(C3=0,1,2-C3),IF(AND(C3>=0,C3<B4),B4-C3,(1-C3)+B4))
Explanation of the code:

Below: IF C4 (clock out time) = B4 (clock in time) Meaning the person did not work at all that day. Then IF C3 (previous clock out time0 = 0 (Midnight) OUTPUT 1 (Integer for 24 hours in excel) ELSE 2 (Integer for 48 hours in excel) &#8211;C3 (previous clock out time).

Code:
 =IF(C4=B4,IF(C3=0,1,2-C3)
IF above C4 does not equal B4 then this next formula will run.

Below: IF C3 (previous clock out time) is greater than or equal to 0 Midnight and before the next clock in time on the same day THEN OUTPUT B4 todays clock in time &#8211; C3 previous clock out time ELSE OUTPUT 1 (Integer for 24 hours in excel) &#8211; C3 (previous clock out time) + B4 (current clock in time)

Code:
 ,IF(AND(C3>=0,C3<B4),B4-C3,(1-C3)+B4))
TIP: I had to change the rest hours format to 00:00:00 to display anything above 24 hours correctly. Also for some reason Excel is programed to know what you mean when you put the 24-C3+B4 instead of giving you some ridiculously large number like it did when I tried 48 hours with the number 48. After finding How to Use Excel's HOUR Function | eHow.com I found out what I was doing wrong.

Please let me know if this needs any more explaining or it does not work for what you need.
- William

Edited: at 3:25PM (EST) for correction on the formula.
 
Last edited:

azzkiker

Newbie
Joined
Feb 25, 2012
Messages
0
Cool!

Thank you so much for your help, I'm starting to understand a bit better... I have one final request if you dont mind.

Is it possible to make the worksheet sum the rest periods across the span of days off, and report in a single cell?

The idea being that if you clocked out on the 2nd at 21:00 and didnt work the 3rd and 4th, but clocked in on the 5th at 06:00, the rest period for the 5th would show as 57:00 on the 5th...

If possible can the cells for the 3rd and 4th (27:00, and 24:00 rest period shown presently) be told to display "off" or something other than the number if it exceeds 23:59, while retaining the data needed for the formula?

Thank you again for your help!
 

azzkiker

Newbie
Joined
Feb 25, 2012
Messages
0
Update

Here is an example of what I meant in the last message the file that might be a bit more descriptive or sensible than my explanation.

Thanks!
 

Attachments

KD8PVX

Member
Joined
Feb 9, 2011
Messages
22
Location
Charlotte, MI
I understand what you are asking and at this point I am not sure if it is possible or not. If I get time in the next few days I will attempt to figure this out. How ever I hope there is someone out there that knows more than I do and is able to pipe in and give the solution :wink:

- William
 
Status
Not open for further replies.
Top