* AvCanada's Home Page * Photo Gallery * Directory * Topsites *Weather *Enter Chat * Media Kit
It is currently Sun May 19, 2013 4:37 pm

View unanswered posts | View active topics




Board index » Announcements & Help » Internet and Computer Help

All times are UTC [ DST ]

Who is chatting
Who is chatting
Enter Chat




Post new topic Reply to topic  Page 1 of 1
  [ 18 posts ] 
  Print view Previous topic | Next topic 
Author Message
Grey_Wolf
 Post subject: Excel Formula Question
PostPosted: Mon Sep 06, 2010 2:34 pm 
Offline
Rank 7
Rank 7
User avatar

Joined: Sat Aug 19, 2006 9:23 pm
Posts: 506
Morning All :mrgreen:

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


Top
 Profile  
 
moocow
PostPosted: Tue Sep 07, 2010 12:41 am 
Offline
Rank 7
Rank 7

Joined: Sat Oct 06, 2007 6:36 am
Posts: 616
Check your data range. The #Value! error probably mean there are empty cells somewhere or meaningless data like text.


Top
 Profile  
 
AuxBatOn
PostPosted: Tue Sep 07, 2010 12:48 am 
Offline
Rank (9)
Rank (9)

Joined: Thu Jan 17, 2008 2:13 am
Posts: 1755
Location: CYBG
Let's say you put "01/09/10 11:30" in cell A1 and "01/09/10 23:30" in cell A2, if you put "A2-A1" in cell A3, you will get "12:00" as a return.


Top
 Profile  
 
Grey_Wolf
PostPosted: Tue Sep 07, 2010 4:28 pm 
Offline
Rank 7
Rank 7
User avatar

Joined: Sat Aug 19, 2006 9:23 pm
Posts: 506
Thanks Guys

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!


Top
 Profile  
 
Grey_Wolf
PostPosted: Tue Sep 07, 2010 8:43 pm 
Offline
Rank 7
Rank 7
User avatar

Joined: Sat Aug 19, 2006 9:23 pm
Posts: 506
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! :prayer:


Top
 Profile  
 
Grey_Wolf
PostPosted: Wed Sep 08, 2010 7:51 pm 
Offline
Rank 7
Rank 7
User avatar

Joined: Sat Aug 19, 2006 9:23 pm
Posts: 506
Dabbled more in Excel today. Found out (via Excel Help) that Excel doesn't count more than 24 hours in a difference between times.


Top
 Profile  
 
AuxBatOn
PostPosted: Wed Sep 08, 2010 8:16 pm 
Offline
Rank (9)
Rank (9)

Joined: Thu Jan 17, 2008 2:13 am
Posts: 1755
Location: CYBG
Make sure you put a date in with a time. Otherwise it will assume today. I was able to make it work yesterday and many times before.


Top
 Profile  
 
200hr Wonder
PostPosted: Wed Sep 08, 2010 9:05 pm 
Offline
Rank 10
Rank 10
User avatar

Joined: Tue Apr 26, 2005 9:52 pm
Posts: 2055
Location: CYVR
Grey Wolf,

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.


Top
 Profile  
 
Louis
PostPosted: Thu Sep 09, 2010 4:05 pm 
Offline
Rank 8
Rank 8

Joined: Mon Feb 16, 2004 3:28 am
Posts: 990
Location: CYUL
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.


Top
 Profile  
 
Grey_Wolf
PostPosted: Sat Sep 11, 2010 12:04 am 
Offline
Rank 7
Rank 7
User avatar

Joined: Sat Aug 19, 2006 9:23 pm
Posts: 506
200Hr Wonder. The INT Formula (B1-A1)*24, works so far. Is there a way to get minutes in there as well?


Top
 Profile  
 
200hr Wonder
PostPosted: Sat Sep 11, 2010 11:06 am 
Offline
Rank 10
Rank 10
User avatar

Joined: Tue Apr 26, 2005 9:52 pm
Posts: 2055
Location: CYVR
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.

Cheers,

200hr Wonder

PS Had to zip the file, just unzip to view.


Attachments:
Dates Example.xls.zip [21.69 KiB]
Downloaded 55 times
Top
 Profile  
 
Grey_Wolf
PostPosted: Sun Sep 12, 2010 2:24 am 
Offline
Rank 7
Rank 7
User avatar

Joined: Sat Aug 19, 2006 9:23 pm
Posts: 506
Cool

Here's my "template" file with a few example/numbers put in.

The yellow highlighted cells are the ones I'm having difficulty solving.

Please let me know if you have any questions.

Thanks A Million!


Top
 Profile  
 
200hr Wonder
PostPosted: Sun Sep 12, 2010 3:02 am 
Offline
Rank 10
Rank 10
User avatar

Joined: Tue Apr 26, 2005 9:52 pm
Posts: 2055
Location: CYVR
Grey Wolf

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.


Top
 Profile  
 
Grey_Wolf
PostPosted: Mon Sep 13, 2010 9:50 pm 
Offline
Rank 7
Rank 7
User avatar

Joined: Sat Aug 19, 2006 9:23 pm
Posts: 506
Thanks for the help so far ...

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


Top
 Profile  
 
200hr Wonder
PostPosted: Tue Sep 14, 2010 4:52 am 
Offline
Rank 10
Rank 10
User avatar

Joined: Tue Apr 26, 2005 9:52 pm
Posts: 2055
Location: CYVR
See Attached


Attachments:
Mileage Report Sheet Fixed.xlsx.zip [19.05 KiB]
Downloaded 53 times
Top
 Profile  
 
Grey_Wolf
PostPosted: Thu Sep 16, 2010 11:46 pm 
Offline
Rank 7
Rank 7
User avatar

Joined: Sat Aug 19, 2006 9:23 pm
Posts: 506
Awesome!

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! :mrgreen:

Cheers!


Top
 Profile  
 
200hr Wonder
PostPosted: Fri Sep 17, 2010 4:32 am 
Offline
Rank 10
Rank 10
User avatar

Joined: Tue Apr 26, 2005 9:52 pm
Posts: 2055
Location: CYVR
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?


Top
 Profile  
 
Grey_Wolf
PostPosted: Fri Sep 17, 2010 10:10 pm 
Offline
Rank 7
Rank 7
User avatar

Joined: Sat Aug 19, 2006 9:23 pm
Posts: 506
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. :wink:


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  Page 1 of 1
  [ 18 posts ] 


Board index » Announcements & Help » Internet and Computer Help

All times are UTC [ DST ]

Who is online

Users browsing this forum: No registered users and 12 guests

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB® Forum Software © phpBB Group

Advertisements by Advertisement Management
 
For questions/comments please send them to
webmaster@avcanada.ca

AvCanada Topsites List
AVIATION TOP 100 - www.avitop.com Avitop.com
While the administrators and moderators of this  forum will attempt to remove or edit any generally objectionable material as  quickly as possible, it is impossible to review every message. If you feel a  topic or post is inappropriate email us at support@avcanada.ca .  By reading these forums you acknowledge that  all posts made to these forums express the views and opinions of the author and  not the administrators, moderators or webmaster (except for posts by these  people) and hence will not be held liable. This website is not responsible or liable in any way for any false or misleading messages or job ads placed at our site.   

Use AvCanada's information at your own risk!

We reserve the right to remove any messages that we deem unacceptable.
  When you post a message, your IP is logged and may be provided to concerned parties where unethical or illegal  behavior is apparent. All rights reserved.