CODEWITHSUNDEEP
excelexcel-formulalogic
Devesh
Devesh

Reputation: 2054

Date time columns falls between two time ranges

To me it looks like more on logical. Here I need to put conditions on both the columns which are in Date time format. I can use helper column.

I have two columns like Dep time and Arr time in Date Time format ( 4/3/2016 10:30:00 AM).

if Dep time and arrival time falls between ( 0600-2159)
       diff b/w ( Dep time and Arr time) /2" 
if Dep time and arrival time falls between ( 2200-0559)  
       diff b/w ( Dep time and Arr time)/1.75

This works if Dep time and arrival time in same date.

=IF(AND(DT>V27,AT<V28),(M2-L2)/2 # Where V27 is 0600 and V28 2159

But if they are in different days, for example dept time is 04/03/2016 04:00:00 AM and Arrival time is 04/05/2016 07:00:00 AM, so I need to calculate that how much time it was in 0600-2159 time window and this 2200-0559 for these days of complete journey.

Appreciate your time! Here is the sample data print screen.

Here is the print screen of sample data

Upvotes: 0

Views: 90

Answers (1)

gtwebb
gtwebb

Reputation: 3011

For the hours between 0600-2159 I would use

=(MAX(MOD(A2,1),TIME(22,0,0))-MAX(MOD(A2,1),TIME(6,0,0)))+
(INT(B2)-INT(A2))*(16/24)+
(MAX(MOD(B2,1),TIME(6,0,0))-MAX(MOD(B2,1),TIME(22,0,0)))

First line considers the departure time, second line considers the numbers of days between dep and arrival and last line deals with arrival time.

the mod function returns you decimal portion of the date/time which is just your time.

The int function will return just the date (no time).

For the other catagory I would just use

=B1-A1-C1

(total time - 0600 to 2159 time)

Upvotes: 2

Related Questions