RadioReference on Facebook   RadioReference on Twitter   RadioReference Blog
 

Go Back   The RadioReference.com Forums > The RadioReference Tavern > Computer

Computer Why pay for computer support when you can find it here

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 02-25-2012, 12:36 AM
   
Join Date: Feb 2012
Posts: 0
Default Excel Help

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?
Attached Files
File Type: zip timelog.zip (7.6 KB, 18 views)
Reply With Quote
Sponsored links
  #2 (permalink)  
Old 02-27-2012, 7:30 AM
KD8PVX's Avatar
Member
  Shack Photos
Shack photos
Amateur Radio Operator
Amateur Radio
 
Join Date: Feb 2011
Location: Charlotte, MI
Posts: 22
Default

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
__________________
Michigan Scanner Law - http://www.legislature.mi.gov/(S(h0l...me=mcl-750-508
Ham exam prep - http://www.hamwhisperer.com
Reply With Quote
  #3 (permalink)  
Old 02-27-2012, 11:17 PM
   
Join Date: Feb 2012
Posts: 0
Smile

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.
Reply With Quote
  #4 (permalink)  
Old 02-29-2012, 1:46 PM
KD8PVX's Avatar
Member
  Shack Photos
Shack photos
Amateur Radio Operator
Amateur Radio
 
Join Date: Feb 2011
Location: Charlotte, MI
Posts: 22
Default

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) –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 – C3 previous clock out time ELSE OUTPUT 1 (Integer for 24 hours in excel) – 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.
__________________
Michigan Scanner Law - http://www.legislature.mi.gov/(S(h0l...me=mcl-750-508
Ham exam prep - http://www.hamwhisperer.com

Last edited by KD8PVX; 02-29-2012 at 2:29 PM..
Reply With Quote
  #5 (permalink)  
Old 02-29-2012, 3:37 PM
   
Join Date: Feb 2012
Posts: 0
Default 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!
Reply With Quote
Sponsored links
  #6 (permalink)  
Old 02-29-2012, 5:25 PM
   
Join Date: Feb 2012
Posts: 0
Default 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!
Attached Files
File Type: zip timelog.zip (11.3 KB, 5 views)
Reply With Quote
  #7 (permalink)  
Old 03-01-2012, 7:33 AM
KD8PVX's Avatar
Member
  Shack Photos
Shack photos
Amateur Radio Operator
Amateur Radio
 
Join Date: Feb 2011
Location: Charlotte, MI
Posts: 22
Default

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

- William
__________________
Michigan Scanner Law - http://www.legislature.mi.gov/(S(h0l...me=mcl-750-508
Ham exam prep - http://www.hamwhisperer.com
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT -5. The time now is 4:42 AM.


Powered by vBulletin® Version 3.8.2
Copyright ©2000 - 2014, vBulletin Solutions, Inc.
All information here is Copyright 2012 by RadioReference.com LLC and Lindsay C. Blanton III.Ad Management by RedTyger
Copyright 2011 by RadioReference.com LLC Privacy Policy  |  Terms and Conditions