• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

formula to figure how many hours [SOLVED]

D

dchau

New Member
Good morning, my name is Deb and I have an excel formula that I need but can't seem to figure out how to do it.


If a driver comes in at 5:00am today and yesterday left work at 17:00pm how many hours went by until they came in today. It should equal 12 hours but I can't seem to get it to calculate correctly. This is what it looks like


IN OUT rest hours

5:00AM 17:00PM 12


any assistance will be appreciated, you've all been so great in helping me with other formulas I know you can help me this time as well. If I need to switch something I can so don't limit your ideas for the formula. Thank you again.


Deb
 
Deb


Time is simply a decimal number

So your answer is

=today()+ In time - (today()-1+ out time )
 
Hi Deb..


(No! Not You.. to myself..:) )


does 17:00 PM means 5:00 AM.. :) because as per EXCEL
Code: 
=DATE(2013,2,34)
means 6-March-2013 :)


so either it will be 1700 HRS or it will be 5:00 AM..

or do I need to contact Driver and ask why he left just after coming..


Regards,

Deb..
 
Hi Hui, i don't really understand what you are saying for =today()+in time-(today()-1+out time.


the out time is yesterdays out time and the in time is today, could you please elaborate on this for me?
 
Hi,


Adding to Hui's answer, this formula will work if you leave and arrive in the same day, and also if you arrive back after midnight, as in your example:


=24*((B1<=A1)+B1-A1)


Where A1 is the departure time and B1 is the arrival time. this will return the result in decimal hours (e.g. 12.5 rather than 12:30). If you want to return the 12:30 version, then remove the 24* bit. If you do this, I'd also recommend formatting your hours column as [h]:mm
 
Hi Deb,


The 24* bit in the formula will convert from a time (e.g. 12:30) to a number of hours (12.5).


The formula assumes your drivers are not away across more than one midnight though.


Hope this helps!
 
sorry if I didn't explain well enough. I have to report the driver's rest time they are required to rest at least 10 hours before driving again so I'm trying to find a formula that can calculate the hours they rested in between shifts without having to do it myself (yes I'm lazy, but also like to streamline things as much as possible so anyone can do it if I'm not here).


so if the driver left yesterday at 17:00 (military for 5:00pm) and arrived this morning at 5:00am the number of hours they rested is 12 hours, this is what I need to calculate.

Thanks to everyone trying to assist me I appreciate it very much.
 
Hi ,


Please understand that dates and times are really just numbers ; a date is the number of days that have elapsed since Dec 31 , 1899 ; thus a date of 1/1/1900 is really the same as the number 1.


Today's date of June 5 , 2013 would be day number 41430.


Another thing to remember about dates is that dates are all integer numbers , and the changeover from one date to another happens at midnight.


Times are also numbers , but decimal numbers ranging from 0 to 1 :


00:00:00 which is midnight on any date is just the number 0


23:59:59 which is 1 second short of midnight is just the number 0.999988425925926


So , you can always add dates and times ; thus today's date is available in Excel through the function =Today() ; yesterday would be just 1 less than today ; thus =Today() - 1 would give you yesterday.


So an Intime of 5 p.m. yesterday would be the same as =Today() - 1 + "17:00:00"


An Outtime of 5 a.m. today would be the same as =Today() + "05:00:00"


The difference is just subtraction :


=Today() + "05:00:00" - ( Today() - 1 + "17:00:00" )


If you put this formula in a cell , and format it as a number , you would get 0.5 , which is half a day of 24 hours. Format the same cell as time ( selecting 13:30:55 i.e. hh:mm:ss ) , and you would get 12:00:00


Narayan
 
P0lar I tried that formula and it didn't work so what am I missing?


DRIVERS HOURS OF SERVICE LOG


TUESDAY

DATE DRIVER IN-TODAY OUT-YESTERDAY REST TIME-HRS

6/4/13 BW 5:08 14:26

6/4/13 MH 6:25 16:39

6/4/13 TA 6:25 17:32

6/4/13 KJ 6:24 16:54

6/4/13 HA 5:20 16:00

6/4/13 MR 4:09 16:26

6/4/13 AD 4:16 15:28
 
Hi ,


These are the results I get by using Hui's formula :

[pre]
Code: 
6/4/2013	BW	5:08	14:26	14:42:00
6/4/2013	MH	6:25	16:39	13:46:00
6/4/2013	TA	6:25	17:32	12:53:00
6/4/2013	KJ	6:24	16:54	13:30:00
6/4/2013	HA	5:20	16:00	13:20:00
6/4/2013	MR	4:09	16:26	11:43:00
6/4/2013	AD	4:16	15:28	12:48:00
[/pre]
Narayan
 
Narayank991, I can't get Hui's formula to work, what am I doing wrong? when I do it it comes up with 4:48:00 PM
 
if today is cell c23 and yesterday is cell d23 how do I do the formula, maybe that's why it's not working
 
You must log in or register to reply here.
Back
Top