Calculate elapsed time excluding specific timeframe

  • Thread starter Thread starter emile.asberg
  • Start date Start date
E

emile.asberg

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

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
 
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...

:)
 
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
 
Hallo Emile,

Am Thu, 14 Mar 2013 12:38:10 -0700 (PDT) schrieb (e-mail address removed):
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.
Click to expand...

try:
=IF(INT(B1)=INT(A1),(11/12-MOD(A1,1))*24,(B1-A1)*24-(INT(B1)-INT(A1))*7)


Regards
Claus Busch
 
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)
Click to expand...

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
 
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
Click to expand...


One must be sure to make the time entries in 24 hr format as well, no?
 
On Wednesday, 13 March 2013 18:31:47 UTC+5:30, (e-mail address removed) 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
 
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
Click to expand...

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
 
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
 
Back
Top