Formula assistance

[Hakunabrotata]

Hi everyone!
Please bear with me as this is my first post so I'm not sure how best to convey a question.

I'm trying to create a formula that will help me look at 2 variables in separate columns - Date/time (Column A) & Days of the week (Column B). From this data I want to identify whether this criteria is flagged as core hours or Non-Core Hours (Column C).

Core Hours
Monday-Friday - 08:30:00-17:30:00

Non-Core Hours
Saturday and Sunday - ALL DAY
Monday Friday - 00:00:00-08:29:59 / 17:30:01-00:00:00

For example,
Row 3 would be core hours as its a weekday and falls between 08.30:00 and 17.30:00
Row 4 would be considered Non-Core as its a weekday but falls above 17:30:00
Row 5 would be Non-core as despite it falling within the timeframe, it falls on the weekend.

1	A	B	C
2	DATE / TIME	Day of the Week	Core/Non-Core
3	01/06/2022 13:15:22	Friday	Core
4	01/06/2022 19:25:20	Friday	Non-Core

5

02/06/2022 11:00:00

Saturday

Non-Core

Can anyone help me create a formula to input into column C to identify whether a row would be considered as core/Non-core. I can always split the time/date so it is just looking at the time element (Apologies as row 5 might seem off-centre)

Any assistance would be greatly appreciated as I've been struggling with this for some time now.

Thank you in advance.

 

[6StringJazzer]

Here you go. I suggest you do more testing with additional real data.

$scratch.xlsm
	A	B	C
1	Date Time	Day of Week	Core/Non-Core
2	1/6/2022 13:15	Thursday	Core
3	1/6/2022 19:25	Thursday	Non-Core
4	2/5/2022 11:00	Saturday	Non-Core
Core Non-core

Cell Formulas
Range	Formula
B2:B4	B2	=A2
C2:C4	C2	=IF(AND(WEEKDAY(A2,2)<=5,MOD(A2,1)>=TIME(8,30,0),MOD(A2,1)<=TIME(17,30,0)),"Core","Non-Core")

 

[Hakunabrotata]

Here you go. I suggest you do more testing with additional real data.

$scratch.xlsm
	A	B	C
1	Date Time	Day of Week	Core/Non-Core
2	1/6/2022 13:15	Thursday	Core
3	1/6/2022 19:25	Thursday	Non-Core
4	2/5/2022 11:00	Saturday	Non-Core
Core Non-core

Cell Formulas
Range	Formula
B2:B4	B2	=A2
C2:C4	C2	=IF(AND(WEEKDAY(A2,2)<=5,MOD(A2,1)>=TIME(8,30,0),MOD(A2,1)<=TIME(17,30,0)),"Core","Non-Core")

Hi 6StringJazzer,

Thank you for the swift response and providing a solution to my predicament! I'll definitely give this a go tomorrow when I log back in.

One day I hope to be as clued up as you when it comes to the world of Excel!

 

[Hakunabrotata]

Hi guys, apologies for creating the other additional post - I didn't mean to clog up the forum. I thought that as I'd selected my first post had been resolved, that I'd have to create another one for this different formula query. Again, sorry for causing any unnecessary work.

Can I ask what the formula would be if the cell only contained the time format Data (HH:MM:SS) and not the date. I have some cells where I just need to know if it fell between 8.30am and 17.30pm.

Any assistance would be greatly appreciated.

Thank you.

 

[Fluff]

How about

=IF(AND(A2>=TIME(8,30,0),A2<=TIME(17,30,0)),"Core","Non-Core")

 

[Hakunabrotata]

How about

=IF(AND(A2>=TIME(8,30,0),A2<=TIME(17,30,0)),"Core","Non-Core")

This worked perfectly Fluff - Thank you for the support.

 

[Fluff]

Glad we could help & thanks for the feedback.