# Excel Help

Status
Not open for further replies.

#### azzkiker

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

• timelog.zip
7.6 KB · Views: 88

#### KD8PVX

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

• timelog.zip
11.3 KB · Views: 71

#### KD8PVX

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

Replies
19
Views
1K
Replies
1
Views
2K
Replies
10
Views
2K
Replies
13
Views
763
Replies
16
Views
1K