PDA

View Full Version : [SOLVED] Time/Date Formula for Analysis



Marcster
07-09-2014, 08:17 AM
Hi People,

I'm currently doing this manually :( and I'm pretty sure there's a formula(s) to do this, as a sample:




Column A
Column B
Column C
Column D
Column E










Weekday
Monday to Friday 09:00 to 17:00
Non Working/Out of Hours
Wednesday 19:30 to 21:00


30/06/2014 16:56
Monday
Y




30/06/2014 17:46
Monday

Y



25/06/2014
Wednesday


Y




In column C I want a 'Y' displayed if:
Column A same row, is a Weekday(Monday to Friday) and the time is between 09:00 and 17:00
Column E I want a 'Y' displayed if:
Column A same row, is a Wednesday and time is between 19:30 and 21:00
Column D I want a 'Y' displayed if:
Column A same row, is not Monday to Friday 09:00 to 17:00 or Wednesday 19:30 to 21:00


Thanks :help

Bob Phillips
07-09-2014, 11:11 AM
Try

C3: =IF(AND(WEEKDAY($A3,2)<6,MOD($A3,1)>=--"09:00:00",MOD($A3,1)<=--"17:00:00"),"Y","")

D3: =IF(OR(WEEKDAY($A3,2)>=6,AND(ISNUMBER(MATCH(WEEKDAY($A3,2),{1,2,4,5},0)),OR(MOD($A3,1)<=--"09:00:00",MOD($A3,1)>=--"17:00:00")),AND(WEEKDAY($A2,2)=3,OR(MOD($A2,1)<=--"19:30:00",MOD($A2,1)>=--"21:00:00"))),"Y","")

E3: =IF(AND(WEEKDAY($A3)=4,MOD($A3,1)>=--"19:30:00",MOD($A3,1)<=--"21:00:00"),"Y","")

Marcster
07-09-2014, 01:03 PM
Thanks XLD :).

Made a slight adjustment in cell D3:
=IF(OR(WEEKDAY($A3,2)>=6,AND(ISNUMBER(MATCH(WEEKDAY($A3,2),{1,2,4,5},0)),OR(MOD($A3,1)<=--"09:00:00",MOD($A3,1)>=--"17:00:00")),AND(WEEKDAY($A3,2)=3,OR(MOD($A3,1)<=--"19:30:00",MOD($A3,1)>=--"21:00:00"))),"Y","")

Now I can drag down the rows and it's done. Thanks again.

Bob Phillips
07-10-2014, 05:29 AM
LOL! I don't know what I was doing there!