count date time overnight

  • Thread starter Thread starter I
  • Start date Start date
I

I

Dear all,

A1 B1 C1 D1
E1
2003/04/30 20:10 2003/05/04 10:30 = datetime of C1,D1-
datetime of A1,B1

criteria : 1. unit of the answer in cell E1 should be in hr
2. 23:00 at night to 07:00 the next day should not be
counted
i.e. 20:00 to 09:00 of the next day should be
counted as 5hrs.

How the formula at E1 should be written?
Thanks.

ims
 
Tere


I said:
Dear all,

A1 B1 C1 D1
E1
2003/04/30 20:10 2003/05/04 10:30 = datetime of C1,D1-
datetime of A1,B1

criteria : 1. unit of the answer in cell E1 should be in hr
2. 23:00 at night to 07:00 the next day should not be
counted
i.e. 20:00 to 09:00 of the next day should be
counted as 5hrs.

How the formula at E1 should be written?
Click to expand...

Kindlasti saaks ka lihtsustada, aga pole selleks aega:

=(C1+D1)-(A1+B1)-((C1+IF(D1<TIME(7;0;0);D1;TIME(7;0;0)))-(A1+IF(B1>TIME(23;0
;0);B1;TIME(23;0;0)))-(INT((C1+IF(D1<TIME(7;0;0);D1;TIME(7;0;0)))-(A1+IF(B1>
TIME(23;0;0);B1;TIME(23;0;0))))*16)/24)


Arvi Laanemets
 
Hi

Sorry, but by my first attempt to answer my mind was wandering somewhere and
I forget that it's english NG here :-((


I said:
Dear all,

A1 B1 C1 D1
E1
2003/04/30 20:10 2003/05/04 10:30 = datetime of C1,D1-
datetime of A1,B1

criteria : 1. unit of the answer in cell E1 should be in hr
2. 23:00 at night to 07:00 the next day should not be
counted
i.e. 20:00 to 09:00 of the next day should be
counted as 5hrs.

How the formula at E1 should be written?
Click to expand...

(Surely can you simplify it):

=(C1+D1)-(A1+B1)-((C1+IF(D1<TIME(7,0,0),D1,TIME(7,0,0)))-(A1+IF(B1>TIME(23,0
,0),B1,TIME(23,0,0)))-(INT((C1+IF(D1<TIME(7,0,0),D1,TIME(7,0,0)))-(A1+IF(B1>
TIME(23,0,0),B1,TIME(23,0,0))))*16)/24)


Arvi Laanemets
 
Hi,

With your LowerBound (7:00) in H1 and your UpperBound (23:00) in H2

In E1:
=MAX($H$1,MIN($H$2,D1))-MAX($H$1,MIN($H$2,B1))-($H$1-$H$2)*(C1-A1)

Format E1 as [h]:mm

Regards,

Daniel M.
 
Hi Arvi, ims,
TIME(23,0,0),B1,TIME(23,0,0))))*16)/24)
Click to expand...

OP has to be careful as working from 20:00 to 23:15 should report 3:00 and
not 3:15

Agree that it's no simple problem: it involves quite a few test cases.

Have a nice day,

Daniel M.
 
Dear Daniel,

Don't really know how to lowerbound(7:00) and upperbound(23:00)
What are the forumlae in H1 & H2?
Thanks.

ims



Daniel.M said:
Hi,

With your LowerBound (7:00) in H1 and your UpperBound (23:00) in H2

In E1:
=MAX($H$1,MIN($H$2,D1))-MAX($H$1,MIN($H$2,B1))-($H$1-$H$2)*(C1-A1)

Format E1 as [h]:mm

Regards,

Daniel M.

I said:
Dear all,

A1 B1 C1 D1 E1
2003/04/30 20:10 2003/05/04 10:30
Click to expand...
E1 = datetime of C1,D1-datetime of A1,B1

criteria : 1. unit of the answer in cell E1 should be in hr
2. 23:00 at night to 07:00 the next day should not be
counted
i.e. 20:00 to 09:00 of the next day should be
counted as 5hrs.

How the formula at E1 should be written?
Thanks.

ims
Click to expand...
Click to expand...
 
Hi,

They're NOT formulae, they're part of your problem definition.
You don't want to count time between 23:00 and 7:00 in the morning.

So just put 7:00 in H1 and 23:00 in H2

Then, everything will work.

Regards,

Daniel M.

I said:
Dear Daniel,

Don't really know how to lowerbound(7:00) and upperbound(23:00)
What are the forumlae in H1 & H2?
Thanks.

ims



Daniel.M said:
Hi,

With your LowerBound (7:00) in H1 and your UpperBound (23:00) in H2

In E1:
=MAX($H$1,MIN($H$2,D1))-MAX($H$1,MIN($H$2,B1))-($H$1-$H$2)*(C1-A1)

Format E1 as [h]:mm

Regards,

Daniel M.

I said:
Dear all,

A1 B1 C1 D1 E1
2003/04/30 20:10 2003/05/04 10:30
Click to expand...
E1 = datetime of C1,D1-datetime of A1,B1

criteria : 1. unit of the answer in cell E1 should be in hr
2. 23:00 at night to 07:00 the next day should not be
counted
i.e. 20:00 to 09:00 of the next day should be
counted as 5hrs.

How the formula at E1 should be written?
Thanks.

ims
Click to expand...
Click to expand...
Click to expand...
 
Back
Top