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,

Thanks for your answer i will give it try.

Emile

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 :

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

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

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?

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

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

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

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