A formula to satisfy a condition for a time range having date also

[vijitkumar]

Hi, I have a date and time column as follows:

08/01/2013 00:54:15
08/01/2013 08:03:01
08/01/2013 09:32:52
08/01/2013 13:03:43
08/01/2013 15:32:44

In the adjoining column, I need to satisfy a condition below: If the time is between 06:30:00 and 18:00:00 then it should return True, else False How can this be made possible?

If in the same file in separate column if I could identify weekdays vs weekends based on the date, it would be very helpful.

 

[xlstime]

Hi Vijit,

Please find attached Solution. Hope its fulfill your requirements

Thanks

 

[vijitkumar]

Works perfectly. Thanks a lot!

However, I am curious to know how the formula works.

Could you please breakup both the formulas for me so that I can understand how you achieved this and what was the logic you applied?

Are there any conditions to be satisfied for the formula to work. Like the format of the date etc?

 

[vijitkumar]

Actually I saw that it is false for following when it should actually be true as it is between 06:30:00 and 18:00:0:
08/02/2013 08:11:05 FALSE

 

[NARAYANK991]

Hi Vijit ,

Dates are essentially numbers , where dates by themselves are integers , and times are decimal numbers , with one day of 24 hours equal to the region from 0 through 1. The date changeover takes place at midnight , so that 00:00:00 ( midnight ) will equal 0 as far as time is concerned ; noon , which is 12:00:00 PM will equal 0.5 , 11:59:59 PM will equal 0.999988425925926

A composite date + time value has an integer component equal to the date , and a decimal component equal to the time.

To take the first example in your uploaded worksheet , the value is :

08/01/2013 00:54:15

This is a text string , and to get the numeric value , we will have to use the VALUE function , which will give us the following :

41487.0376736111

In this , the integer part 41487 is the date i.e. 08/01/2013 , while the decimal part 0.0376736111 is the time part 00:54:15

To verify this , in any unused cell enter the value 41487 ; change the format to date , and see what is displayed. Similarly , enter the value 0.0376736111 ; change the format to time , and see what is displayed.

In any number which has both an integer part and a decimal part , isolating the decimal part can be done by :

=MOD(number,1)

where number can be 41487.0376736111

To check whether this time is within the window that you have given , you can use :

=AND(MOD(number,1)>=TIMEVALUE("06:30:00"),MOD(number,1)<=TIMEVALUE("18:00:00"))

Narayan

 

[vijitkumar]

Hi Narayan,

I am not sure whether you saw my last reply on this one.

"Actually I saw that it is false for following when it should actually be true as it is between 06:30:00 and 18:00:00:
08/02/2013 08:11:05 FALSE"

 

[NARAYANK991]

Hi Vijit ,

I think you have confused my post with that of another forum member. Please clarify.

Narayan

 

[vijitkumar]

yes Narayan, you are right.

Sorry about that.

It was meant for xlstime who provided me with the solution