count date time overnight

[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

 

[Arvi Laanemets]

Tere

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?

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

 

[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 :-((

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?

(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

 

[I]

Wow~~
What a complicated formula!
I'll try to apply it to my worksheet, thanks.

ims

 

[Daniel.M]

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.

 

[Daniel.M]

Hi Arvi, ims,

TIME(23,0,0),B1,TIME(23,0,0))))*16)/24)

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.

 

[I]

Dear Daniel,

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

ims

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.

Dear all,

A1 B1 C1 D1 E1
2003/04/30 20:10 2003/05/04 10:30

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

 

[Daniel.M]

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.

Dear Daniel,

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

ims

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.

Dear all,

A1 B1 C1 D1 E1
2003/04/30 20:10 2003/05/04 10:30

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

 

[Daniel.M]

You're welcome.

Daniel M.