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

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

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.

An example file has been attached to the ticket.

2. ## Re: A formula to satisfy a condition for a time range having date also

For the TimeRange
=AND(MOD(A2,1)>="06:30:00"+0,MOD(A2,1)<="18:00:00"+0)

For weekday vs weekend
=IF(WEEKDAY(A2,2)>5,"Weekend","Weekday")

3. ## Re: A formula to satisfy a condition for a time range having date also

use the attached file

if there is any further query then feel free to ask.

if it was really helpful to you then dont forget to hit * button for user reputation.

Regards
CA Mahaveer Somani

4. ## Re: A formula to satisfy a condition for a time range having date also

Hi, Vijitkumar

Put this formula in cell B2:

=IF(AND(B2<TIME(18,0,0),B2>TIME(5,59,59)),"TRUE","FALSE")

Copy down

5. ## Re: A formula to satisfy a condition for a time range having date also

Originally Posted by Rambo4711
=IF(AND(B2<TIME(18,0,0),B2>TIME(5,59,59)),"TRUE","FALSE")
Don't see how that will work because:
1. The values are in column A
2. The values are DATE+Time, not just time.
So none of the values are < TIME(18,0,0)

You have to extract the time from the cell that contains Date+Time
I used MOD for that purpose.

6. ## Re: A formula to satisfy a condition for a time range having date also

@Jonmo1

Just edited my post and attached a file. Can you see any wrong answers in the formulas?

7. ## Re: A formula to satisfy a condition for a time range having date also

Originally Posted by Rambo4711
@Jonmo1

Just edited my post and attached a file. Can you see any wrong answers in the formulas?
Yes.

The OP's File has Date+Time in Column A.
How are you getting from Date+Time in A, to having
Date in A and Time in B ?

8. ## Re: A formula to satisfy a condition for a time range having date also

ROMBO USED MY TRick @ JONMO as i already post.

and @jonmo your formula is not working........

9. ## Re: A formula to satisfy a condition for a time range having date also

Originally Posted by mahaveersomani
and @jonmo your formula is not working........
In what way is mine not working on the file attached by the OP??

EFvijitkumar.xlsx

There are currently 1 users browsing this thread. (0 members and 1 guests)