Days from dates

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi,
I have raw data in a colum which is the following format eg.20/11/2003
16:23 for date and time. I need to extract a value which is either Day,
Evening or Weekend depending on the date and time. I need day to run from
8:00am to 18:00pmMon-Fri, evening to go from 18:00pm to 8:00am,and Weekend
to run from Friday Midnight to Monday 8:00am. Will a simple IF statment do
it, and if so how? Any help will be greatfully recieved. John
 
Hi John!

Try:
=IF(OR(WEEKDAY(A1)=7,WEEKDAY(A1)=1,AND(WEEKDAY(A1)=2,MOD(A1,1)<=(8/24)
)),"Weekend",IF(OR(MOD(A1,1)<=(8/24),MOD(A1,1)>=(18/24)),"Evening","Da
y"))

Note that we've excluded weekend in the first IF function and that the
second IF function only needs to test for Evening or Day of the date /
times that get through to FALSE in the first IF function.

Since times are recorded as a decimal part of a day, time tests are
done on the MOD(A1,1).
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
=IF(WEEKDAY(A1,2)>5,"Weekend",IF(WEEKDAY(A1,2)=1,IF(HOUR(A1)<8,"Weekend",IF(HOUR(A1)<18,"Day","Evening")),IF(HOUR(A1)<8,"Evening",IF(HOUR(A1)<18,"Day","Evening"))))
 
As ever Norman/Mike........thanks a million, its a great support site here.
I will test this out tonight and try to figure how you worked the logic.
John
 
Hi John!

Re:
=IF(OR(WEEKDAY(A1)=7,WEEKDAY(A1)=1,AND(WEEKDAY(A1)=2,MOD(A1,1)<=(8/24)
)),"Weekend",IF(OR(MOD(A1,1)<=(8/24),MOD(A1,1)>=(18/24)),"Evening","Da
y"))

To understand the logic of my approach consider

=OR(WEEKDAY(A1)=7,WEEKDAY(A1)=1,AND(WEEKDAY(A1)=2,MOD(A1,1)<=(8/24)))
This returns TRUE in three cases:
The day is a Saturday
Or
The day is a Sunday
Or
The day is a Monday AND it's before 8AM

For any of these options you'll get Weekend returned.

If none of hose applies it will be FALSE. Note that if it returns TRUE
we don't process any further and thus we don't have to test again for
day of week or before 8:00 on Mondays.

I use MOD(A1,1) to strip off the integer and get left with the decimal
(time) portion of the date / time serial number.

=OR(MOD(A1,1)<=(8/24),MOD(A1,1)>=(18/24))
This returns TRUE in two cases:
It's before 8AM
Or:
It's after 6:00PM

For any of these options you'll get Evening returned.

If none of these applies, then it returns FALSE and the result must be
Day as this is the only other possibility.

You will get an annoyance if there is no entry in the cell. These will
result in a return of Weekend. To avoid this use:

=IF(A1="","",IF(OR(WEEKDAY(A1)=7,WEEKDAY(A1)=1,AND(WEEKDAY(A1)=2,MOD(A
1,1)<=(8/24))),"Weekend",IF(OR(MOD(A1,1)<=(8/24),MOD(A1,1)>=(18/24)),"
Evening","Day")))

That's a standard approach based on the structure:
=IF(A1="","",YourFormula)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top