Formula assistance

Hakunabrotata

New Member
Joined
Jul 6, 2022
Messages
8
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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.


1ABC
2DATE / TIMEDay of the WeekCore/Non-Core
301/06/2022 13:15:22FridayCore
401/06/2022 19:25:20FridayNon-Core
502/06/2022 11:00:00SaturdayNon-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.
 

Excel Facts

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

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
2,662
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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")
 
Upvote 0
Solution

Hakunabrotata

New Member
Joined
Jul 6, 2022
Messages
8
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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! 😁
 
Upvote 0

Hakunabrotata

New Member
Joined
Jul 6, 2022
Messages
8
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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.
 
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,540
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=IF(AND(A2>=TIME(8,30,0),A2<=TIME(17,30,0)),"Core","Non-Core")
 
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,540
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top