Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time formula over two days
I do appreciate everyones help but I have to try and explain the whole
problem again so its clear and someone might be able to help, I'm having a hard time getting from my head to the question box, I am trying to create an actual v. planned report in excel: Here is the scenario: Our drivers have a standing appointment at the stores We are considered "Early" if we arrive more than 15 mins before the appointment We are considered "Late" if we arrive more than 30 mins after the appointment So our "On Time"delivery window is 15 mins before and 30 mins after the appointment. I will enter the EST time and ACTUAL time manually I need a formula to calculate the varience between the two times as well I want a column to show if the driver was "Late" , "Early" or "Ontime" I have attached a screen shot of the manual table. A B C D ActualArrival Time Est Arrival Time Variance 23:26 0:26 0 on time 22:25 0:26 0 on time 0:10 0:26 0 on time 23:26 0:27 0 on time Cheers!!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time formula over two days
On Fri, 21 May 2010 08:43:01 -0700, Norm
wrote: I do appreciate everyones help but I have to try and explain the whole problem again so its clear and someone might be able to help, I'm having a hard time getting from my head to the question box, I am trying to create an actual v. planned report in excel: Here is the scenario: Our drivers have a standing appointment at the stores We are considered "Early" if we arrive more than 15 mins before the appointment We are considered "Late" if we arrive more than 30 mins after the appointment So our "On Time"delivery window is 15 mins before and 30 mins after the appointment. I will enter the EST time and ACTUAL time manually I need a formula to calculate the varience between the two times as well I want a column to show if the driver was "Late" , "Early" or "Ontime" I have attached a screen shot of the manual table. A B C D ActualArrival Time Est Arrival Time Variance 23:26 0:26 0 on time 22:25 0:26 0 on time 0:10 0:26 0 on time 23:26 0:27 0 on time Cheers!!! It would be more helpful if you had responded to the original suggestions in your original thread. It saves people time in trying to help you, and in repeating suggestions that did not work. What was the problem with my suggestion that you enter both dates and times, and use the 1904 date system to display negative times? --ron |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time formula over two days
"Ron Rosenfeld" wrote: On Fri, 21 May 2010 08:43:01 -0700, Norm wrote: I do appreciate everyones help but I have to try and explain the whole problem again so its clear and someone might be able to help, I'm having a hard time getting from my head to the question box, I am trying to create an actual v. planned report in excel: Here is the scenario: Our drivers have a standing appointment at the stores We are considered "Early" if we arrive more than 15 mins before the appointment We are considered "Late" if we arrive more than 30 mins after the appointment So our "On Time"delivery window is 15 mins before and 30 mins after the appointment. I will enter the EST time and ACTUAL time manually I need a formula to calculate the varience between the two times as well I want a column to show if the driver was "Late" , "Early" or "Ontime" I have attached a screen shot of the manual table. A B C D ActualArrival Time Est Arrival Time Variance 23:26 0:26 0 on time 22:25 0:26 0 on time 0:10 0:26 0 on time 23:26 0:27 0 on time Cheers!!! It would be more helpful if you had responded to the original suggestions in your original thread. It saves people time in trying to help you, and in repeating suggestions that did not work. What was the problem with my suggestion that you enter both dates and times, and use the 1904 date system to display negative times? --ron . Yes I realize splitting the thread wastes time and i apoligize. I was asked to add another part to this the "early late on time " part so I redid the whole thread... I did have a problem using the 1904 date system mostly because I have no knowledge of how that works or is set up Cheers!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time formula over two days
I you can include the date, this is what you get:
A B C D Actual Est Variance Status 5/20/10 23:26 5/21/10 0:26 60 Early 5/20/10 22:25 5/21/10 0:26 121 Early 5/21/10 0:10 5/21/10 0:36 26 On Time 5/21/10 1:56 5/20/10 23:02 -174 Late 5/21/10 1:56 5/21/10 1:45 -11 On Time Formula in C2: =(B2-A2)*24*60 (format as number, NOT as time!) Formula in D2: =IF(AND(C2-15,C2<30),"On Time",IF(C2<=-15,"Late","Early")) Is this what you're trying to achieve? HTH, Eric "Norm" wrote: I do appreciate everyones help but I have to try and explain the whole problem again so its clear and someone might be able to help, I'm having a hard time getting from my head to the question box, I am trying to create an actual v. planned report in excel: Here is the scenario: Our drivers have a standing appointment at the stores We are considered "Early" if we arrive more than 15 mins before the appointment We are considered "Late" if we arrive more than 30 mins after the appointment So our "On Time"delivery window is 15 mins before and 30 mins after the appointment. I will enter the EST time and ACTUAL time manually I need a formula to calculate the varience between the two times as well I want a column to show if the driver was "Late" , "Early" or "Ontime" I have attached a screen shot of the manual table. A B C D ActualArrival Time Est Arrival Time Variance 23:26 0:26 0 on time 22:25 0:26 0 on time 0:10 0:26 0 on time 23:26 0:27 0 on time Cheers!!! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time formula over two days
These two worked for me ,Thank You..... but I wanted to not have to enter the
date as well , the times will come from another program that exports them to Excel and the idea was to cut and paste them in "EricG" wrote: I you can include the date, this is what you get: A B C D Actual Est Variance Status 5/20/10 23:26 5/21/10 0:26 60 Early 5/20/10 22:25 5/21/10 0:26 121 Early 5/21/10 0:10 5/21/10 0:36 26 On Time 5/21/10 1:56 5/20/10 23:02 -174 Late 5/21/10 1:56 5/21/10 1:45 -11 On Time Formula in C2: =(B2-A2)*24*60 (format as number, NOT as time!) Formula in D2: =IF(AND(C2-15,C2<30),"On Time",IF(C2<=-15,"Late","Early")) Is this what you're trying to achieve? HTH, Eric "Norm" wrote: I do appreciate everyones help but I have to try and explain the whole problem again so its clear and someone might be able to help, I'm having a hard time getting from my head to the question box, I am trying to create an actual v. planned report in excel: Here is the scenario: Our drivers have a standing appointment at the stores We are considered "Early" if we arrive more than 15 mins before the appointment We are considered "Late" if we arrive more than 30 mins after the appointment So our "On Time"delivery window is 15 mins before and 30 mins after the appointment. I will enter the EST time and ACTUAL time manually I need a formula to calculate the varience between the two times as well I want a column to show if the driver was "Late" , "Early" or "Ontime" I have attached a screen shot of the manual table. A B C D ActualArrival Time Est Arrival Time Variance 23:26 0:26 0 on time 22:25 0:26 0 on time 0:10 0:26 0 on time 23:26 0:27 0 on time Cheers!!! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time formula over two days
On Fri, 21 May 2010 10:06:12 -0700, Norm
wrote: I did have a problem using the 1904 date system mostly because I have no knowledge of how that works or is set up Cheers!! Depending on your version of Excel, it will be either under Tools/Options; or in the Excel Options area of Excel 2007+ If you don't enter dates and times, unless you have some maximum amount of time a shipment can be early or late, there will be ambiguity. --ron |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time formula over two days
"but I wanted to not have to enter the date as well"
Understood, but a time with no date can be misinterpreted, as you have seen, at the crossover points. Perhaps you can figure out a way to get this other program to spit out the date as well. Good luck, Eric "Norm" wrote: These two worked for me ,Thank You..... but I wanted to not have to enter the date as well , the times will come from another program that exports them to Excel and the idea was to cut and paste them in |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FORMULA, DAYS WORKED TO VACATION DAYS | Excel Worksheet Functions | |||
Re Change 5 week days to 7 days in this formula | Excel Worksheet Functions | |||
Time between days | Excel Worksheet Functions | |||
Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions | |||
Elapsed time in days | Excel Worksheet Functions |