Condition formula based on the time gap between two dates and Time

[rajreddy]

Hi All,

I have attached the sample file .. I have Date opened time and closed time ... I need the Gap between the two times to be divided into the buckets I have mentioned in the column H ...Please insert the relevant function in Column E(In Green)

Thanks
R@J

 

[Chihiro]

See attached sample (just for Same Day, Within 24 hours & 1-4 days).
Follow same principle for 5-15 & 16-30 days.

Note the order of CF and "Stop if True" for Same Day. Stopping further check from happening if it is True for Same Day check. Since if it belongs to Same Day then it will also be true for Within 24 hours.

Also, I've got question.

Is 1 month considered 30 days, 31 days or will it vary?
This will impact how logical check should be performed.

 

[rajreddy]

See attached sample (just for Same Day, Within 24 hours & 1-4 days).
Follow same principle for 5-15 & 16-30 days.

Note the order of CF and "Stop if True" for Same Day. Stopping further check from happening if it is True for Same Day check. Since if it belongs to Same Day then it will also be true for Within 24 hours.

Also, I've got question.

Is 1 month considered 30 days, 31 days or will it vary?
This will impact how logical check should be performed.

> 1 month is greater than 30 days .. > 2 months is greater than 60 days..Please help me with the if condition function so that output comes column E ...

Thanks

 

[Chihiro]

See attached. Added Floor for buckets except for same day.

=IF(INT($D4)=INT($C4),$H$4,LOOKUP($D4-$C4,$G$5:$G$10,$H$5:$H$10))

 

[rajreddy]

I need a function in Column E so that buckets get automatically updated in Column E (Where I entered the data manually)

 

[Chihiro]

Did you open the workbook attached? That's what I gave you in post above no?

 

[rajreddy]

Thank you .. I have got the answer ...

There is one more request ... Can you please exclude Saturday and Sunday from the number of days (Networkdays) and update the function ...

 

[Chihiro]

How do you want to treat where Date Closed fall on weekend then?

Ex: Date Opened 8/12/2016 14:59, Date Closed 8/13/2016 14:00

What's the correct answer? Within 24 hours? Or will it be 1-4 days?

Note: NETWORKDAYS will only consider Date portion of DateTime value and will ignore Time. Therefore you will need additional check for Within 24 hours.

 

[rajreddy]

You can ignore the Saturday's and Sunday's in the sample data ... Please help me in excluding the Saturday's and Sunday's from the number of days (without using network days).. I have Just suggested networkdays .. You can apply any condition to get the output..

 

[rajreddy]

Could you please help me with the function as i need this function for a report to be published

 

[rajreddy]

Attached the file excluding the weekends

 

[Chihiro]

See attached.

Note that there was error in expected result in Row 12. Should be 16-30 days instead of >1 month as there's 25 workdays in the period.

FYI - I'm in EST and usually don't check forum after 1700.

 

[rajreddy]

Thanks You so much for help ... Sorry for the inconvenience..

 

[rajreddy]

Thanks You so much for help ... Sorry for the inconvenience..

Hi Chihiro,

the second condition in the function with in 24 hours has to be corrected bcoz if the date opened falls on any friday and date closed is on monday with in 24 hours .. it has to be with in 24 hours ... but the current function output is showing as 1-4 days ...i have attached the sample ... please help

 

[Chihiro]

You need additional IF statement then. Something like below.

=IF(INT(D4)=INT(C4),$K$4,IF((D4-C4)<1,$K$5,IF(AND((INT(D4)-INT(C4))>NETWORKDAYS(C4,D4),NETWORKDAYS(C4,D4)=2),$K$5,LOOKUP(NETWORKDAYS(C4,D4),$J$5:$J$10,$K$5:$K$10))))

 

[rajreddy]

It is not taking time into consideration for With in 24 Hours ... The whole day is getting calculated post the weekend

 

[Chihiro]

Add another check within AND statement. MOD(D4,1)<MOD(C4,1)