Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate elapsed time excluding specific timeframe
Hello,
Can anybody help me get the formula for the following: I need to calculate the elapsed time between two specific dates/time but exclude the amount which is between 22:00 - 05:00 Example Start date : 1-mar 2013 14:00 End date : 4-mar 2013 07:00 Total elapsed time is 65 hours minus 3x7=21 (hrs between 22:00-05:00) = 44 hours Preferably date and time are two seperate cells (A1 - B1) Result to be calculated in C1 If not possible start time and date as one cell (1-mar-2013 14:00) Hope what i want is possible. Thanks up forehand. Excel noob , Emile |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate elapsed time excluding specific timeframe
Hi Emile
The way I see it is you can do this using Helper Cells. Using your current Date/Time Format: A2 = Start Date/Time B2 = Finish Date Time C2 = "=(($B2-$A2)*24)-(ROUND(($B2-$A2),0)*7)" *Remove the double quotes from C2 HTH Mick |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate elapsed time excluding specific timeframe
And of course it always help to not add to the confusion by actually
reading stuff before submitting it. Emile Disregard the 1st line remark relating to Helper Cells as they are not required... :) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate elapsed time excluding specific timeframe
Mick,
Thanks for your answer i will give it try. Emile |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate elapsed time excluding specific timeframe
Mick,
Again thanks for your help. It not quite want i need yet. For longer periods (24 hrs) it works fine. I want to calculate only the hours used between 0500 - 2200 When i use above times on the same date the result should be 17. It now gives 10 as a result. Emile |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate elapsed time excluding specific timeframe
Hallo Emile,
Am Thu, 14 Mar 2013 12:38:10 -0700 (PDT) schrieb : I want to calculate only the hours used between 0500 - 2200 When i use above times on the same date the result should be 17. It now gives 10 as a result. try: =IF(INT(B1)=INT(A1),(11/12-MOD(A1,1))*24,(B1-A1)*24-(INT(B1)-INT(A1))*7) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate elapsed time excluding specific timeframe
Hallo Emile,
Am Thu, 14 Mar 2013 20:58:56 +0100 schrieb Claus Busch: =IF(INT(B1)=INT(A1),(11/12-MOD(A1,1))*24,(B1-A1)*24-(INT(B1)-INT(A1))*7) sorry, but still a mistake in the formula Try: =IF(INT(B1)=INT(A1),(MIN(11/12,MOD(B1,1))-MOD(A1,1))*24,(B1-A1)*24-(INT(B1)-INT(A1))*7) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate elapsed time excluding specific timeframe
On Thu, 14 Mar 2013 23:19:38 +1100, Living the Dream
wrote: Hi Emile The way I see it is you can do this using Helper Cells. Using your current Date/Time Format: A2 = Start Date/Time B2 = Finish Date Time C2 = "=(($B2-$A2)*24)-(ROUND(($B2-$A2),0)*7)" *Remove the double quotes from C2 HTH Mick One must be sure to make the time entries in 24 hr format as well, no? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate elapsed time excluding specific timeframe
On Wednesday, 13 March 2013 18:31:47 UTC+5:30, wrote: Hello, Can anybody help me get the formula for the following: I need to calculate the elapsed time between two specific dates/time but exclude the amount which is between 22:00 - 05:00 Example Start date : 1-mar 2013 14:00 End date : 4-mar 2013 07:00 Total elapsed time is 65 hours minus 3x7=21 (hrs between 22:00-05:00) = 44 hours Preferably date and time are two seperate cells (A1 - B1) Result to be calculated in C1 If not possible start time and date as one cell (1-mar-2013 14:00) Hope what i want is possible. Thanks up forehand. Excel noob , Emile
Hi Emile Taking a cue from Claus's formula and tweaking the same... =IF(INT(B1)=INT(A1),(MIN(11/12,MOD(B1,1))-MAX(5/24,MOD(A1,1)))*24,(B1-A1)*24-(INT(B1)-INT(A1))*7) This should take into account for hours between 5:00 and 22:00. Hope this helps San |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate elapsed time excluding specific timeframe
Hi San,
Am Sat, 16 Mar 2013 03:58:12 -0700 (PDT) schrieb shriil: =IF(INT(B1)=INT(A1),(MIN(11/12,MOD(B1,1))-MAX(5/24,MOD(A1,1)))*24,(B1-A1)*24-(INT(B1)-INT(A1))*7) without IF-statement: =(MIN(MAX(B2,INT(B2)+5/24),INT(B2)+11/12)-MIN(MAX(A2,INT(A2)+5/24),INT(A2)+11/12))*24-(INT(B2)-INT(A2))*7 Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate elapsed time excluding specific timeframe
On Wednesday, 13 March 2013 18:31:47 UTC+5:30,
wrote: Hello, Can anybody help me get the formula for the following: I need to calculate the elapsed time between two specific dates/time but exclude the amount which is between 22:00 - 05:00 Example Start date : 1-mar 2013 14:00 End date : 4-mar 2013 07:00 Total elapsed time is 65 hours minus 3x7=21 (hrs between 22:00-05:00) = 44 hours Preferably date and time are two seperate cells (A1 - B1) Result to be calculated in C1 If not possible start time and date as one cell (1-mar-2013 14:00) Hope what i want is possible. Thanks up forehand. Excel noob , Emile Hi Emile Taking a cue from Claus's formula and tweaking the same... =IF(INT(B1)=INT(A1),(MIN(11/12,MOD(B1,1))-MAX(5/24,MOD(A1,1)))*24,(B1-A1)*24-(INT(B1)-INT(A1))*7) This should take into account for hours between 5:00 and 22:00. Hope this helps San Actually, the hours to subtract are 22 to 5 NOT 5 to 22. Your formual works for the 2nd timeframe scenario but returns 65 hrs in the 1st timeframe scenario, not the expect 44 hrs. Claus's original formula returns correct hrs in both timeframe scenarios. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate elapsed time excluding specific timeframe
My apologies!!
I retract my report of an error in your formula! After carefull review I found a typo that once I corrected it the correct (expected) value was returned... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to calculate time percentage of number of parts being pick in a timeframe | Excel Worksheet Functions | |||
Calculate elapsed time Excel 2003 | Excel Discussion (Misc queries) | |||
Calculate Ending time using Start Time and Elapsed Time | Excel Worksheet Functions | |||
Calculate Elapsed Date and Time | Excel Worksheet Functions | |||
calculate elapsed time between dates and times | Excel Worksheet Functions |