# Formula assistance

#### Hakunabrotata

##### New Member
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.

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### 6StringJazzer

##### Well-known Member
Here you go. I suggest you do more testing with additional real data.

\$scratch.xlsm
ABC
1Date TimeDay of WeekCore/Non-Core
21/6/2022 13:15ThursdayCore
31/6/2022 19:25ThursdayNon-Core
42/5/2022 11:00SaturdayNon-Core
Core Non-core
Cell Formulas
RangeFormula
B2:B4B2=A2
C2:C4C2=IF(AND(WEEKDAY(A2,2)<=5,MOD(A2,1)>=TIME(8,30,0),MOD(A2,1)<=TIME(17,30,0)),"Core","Non-Core")

#### Hakunabrotata

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

\$scratch.xlsm
ABC
1Date TimeDay of WeekCore/Non-Core
21/6/2022 13:15ThursdayCore
31/6/2022 19:25ThursdayNon-Core
42/5/2022 11:00SaturdayNon-Core
Core Non-core
Cell Formulas
RangeFormula
B2:B4B2=A2
C2:C4C2=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

##### New Member
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

##### MrExcel MVP, Moderator
Excel Formula:
``=IF(AND(A2>=TIME(8,30,0),A2<=TIME(17,30,0)),"Core","Non-Core")``

#### Hakunabrotata

##### New Member
Excel Formula:
``=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

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

Replies
2
Views
156
Replies
1
Views
160
Replies
1
Views
124
Replies
13
Views
504
Replies
5
Views
168

1,186,431
Messages
5,957,797
Members
438,320
Latest member
Thatisfree

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back