Calculate elapsed time excluding specific timeframe


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
 
Ad

Advertisements

L

Living the Dream

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
 
L

Living the Dream

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

:)
 
E

emile.asberg

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
 
C

Claus Busch

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.

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


Regards
Claus Busch
 
Ad

Advertisements

C

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)

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
 
C

CellShocked

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?
 
S

shriil

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
 
G

GS

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
 
Ad

Advertisements

G

GS

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top