Wondering if anyone out there could help me with an MS Excel Question.
What I would like to do is, set an a formula that counts time between two values of time on different days and spits out a number of hours between the two. Essentially counting "duty rest" to be specific.
I have a formula to count duty time, namely =Sum(End Time+Start Time). I have tried using the same formula for duty rest, but excel spits out the #value! error.
Any help would be greatly appreciated. Thanks & Regards
---------- ADS -----------
"A good traveller has no fixed plan and is not intent on arriving." -Lao Tzu
Moocow: I did have empty cells due to the layout of the cells; I have 5 cells set up in a row with start, split start, split end, end, duty. Since the "split" cells are empty, the #value! error would come up.
AuxBatOn: Thanks for pointing out the date format. I changed the cells to it. Problem solved!
---------- ADS -----------
"A good traveller has no fixed plan and is not intent on arriving." -Lao Tzu
One more issue. I have set up the cells in the time format as mentionned by AuxBatOn, the issue now is that Excel doesn't count days between two values.
For example: worked till, let's say 18:00 on day 1, then had 3 days off, then reported at 6:00. Excel seems to only count the hours between 18 and 6 (giving a value of 12:00), but not the extra 72 hours between.
Any Ideas?
Thanks!
---------- ADS -----------
"A good traveller has no fixed plan and is not intent on arriving." -Lao Tzu
Time seems to bugger a lot of people up when it comes to Excel. First of all, dates are serialized to simply a number.
For example 10-Apr-2010 is treated as 38816. Time is a decimal factor after it. IE 38816.50 would be 12 noon on 10-Apr-2010. April 11 is 38817.
Once you understand this manipulating dates becomes much easier.
And here is a useless peace of information, Excel is time indexed from January 1st, 1904. Don't ask me why.
Now back to your question, if you wish to know the number of days between two dates you take the difference between the two and you format it as a number. If you wish to not round there formulas for that. For example if your dates are:
4/10/10 8:00 4/11/10 21:00
The difference between them is 1.54. if you wish to know just the number of days and select to show 1 decimal place, Excel will follow proper rounding procedures and round up to 2 days. To get the correct answer 1 you can apply the formula INT which rounds down to the nearest Integer. So =INT(B1-A1) set to show zero decimal places and voila. If you wanted to know the number of hours between a duty day you could simply do this: =(B1-A1)*24 and you can get your hours, 37 in the example.
While maybe not directly applicable to your calculations, it may be wise to note that if you start using seconds in there, that some rounding errors with the serial value could mean that what shows as 2010-09-09 11:01:35 in two cells would not return equal as true.
Grey Wolf, see the attached sample workbook. There is a sample there where you can enter Day 1 & Day 2 and it will return Days, Hours, Minutes between the dates and a few sample dates. As with all things in Excel there is many ways to do the same thing, so if you have something else that works, it is not necessary more or less correct.
S12 is the example I gave above, if you change it to the date/time format you get 1/4/00 10:35AM which is correct.
What I recommend is this: =(N17-Q12)*24 set this to be a Number with 1 decimal place and you will get your rest as decimal hours, which from what I understand from the sheet is what you are looking for. In fact I would suggest you should do the same with your duty time. What you are doing is is counting hours in both instances. Now if you must use the 23:30 type of notation you can create a custom mask to display it.
The other suggestion I would make is that you can in a hidden cell create the full date time (it comes form Other Places) because having to key in the entire date/time is just a PITA when you just want to punch in some times and be done with it. Also will save many key punch errors. Then do the mat on the hidden cell.
The other issue I see with this is there is no accounting for rest prior to the 1st. Perhaps that needs to be accounted for?
What is your ultimate goal for this sheet? Send it out to people or just for you? As it stands I see quite a few pitfalls right that could just totally screw the sheet up with just a few key punch errors.
I have converted the "Duty" and "Rest" into number/decimal format. Much easier to work with when calculating/summing at the bottom.
The "Rest" prior to the first is not important for the overall use of the sheet, since the sheet is designed to be used on a month-to-month basis, as opposed to a rolling/sliding window type of file (using LOOKUP functions).
Ultimately, the sheet will be distributed to those at work that might like it; however, I'm trying to make it user friendly.
The date entering is a PITA! Any examples on how to do so? (as suggested with a hidden cell).
Any other pitfalls you can point out would be helpful.
Thanks
---------- ADS -----------
"A good traveller has no fixed plan and is not intent on arriving." -Lao Tzu
Been busy at work, sorry for the delay in the response.
Thanks again 200hr Wonder. If you're ever in YXL Terminal, look for the tall bearded fellow, without glasses, flying for the 'rising sun' company. I'll get you a coffee/tea/hot chocolate!
Cheers!
---------- ADS -----------
"A good traveller has no fixed plan and is not intent on arriving." -Lao Tzu
Will do, alas I use to be based out of YXL, really should update the profile as I have returned to wet coast. Though to be honest I had guessed that your sun was rising due to the types on the sheet.
As for the rest period if you just show it for each day you should be good after all you really just want to make sure that you are legal and have sufficient rest from your previous rest correct?
Yup, Duty Rest is what I want to track. So far the sheet does so and I can see if there any times where it might be close for the "36 hours in 7 Days" clause. Anywho, offer still stands.
---------- ADS -----------
"A good traveller has no fixed plan and is not intent on arriving." -Lao Tzu
