Date and time formula

[xxsalahxx]

Hello I have a spreadsheet with a lot of data, Column A shows the Day/Date for example in Cell A2 Monday/01/06/2020
In Column B I have the time in 24hr format 02:14. Im looking for a formula that will show in Column C if the Date falls on a Weekday (Monday to Friday) between the hours of 08:00 and 17:00 this will show "Within Hours" any thing out with this will be "Out of Hours".

If the day is a Saturday or a Sunday this will also show "out of hours". Is there a formula for this? Thanking you in advance for any help.

Thank

 

[Dave Patton]

Is A2 a real date or is it Text?
Is 01 the day or the month?

 

[6StringJazzer]

What is the actual value in column A? Is this a date formatted as "Dddd dd/mm/yyyy", or is it text typed in just as you've shown? I recommend you use true date values when you are dealing with dates.

If you have real dates then this:

$scratch.xlsm
	A	B	C
1	Monday 01/06/2020	02:14	Out of Hours
2	Tuesday 01/07/2020	08:00	Within Hours
3	Wednesday 01/08/2020	07:59	Out of Hours
4	Thursday 01/09/2020	17:01	Out of Hours
5	Friday 01/10/2020	17:00	Within Hours
6	Saturday 01/11/2020	10:00	Out of Hours
7	Sunday 01/12/2020	16:00	Out of Hours
date hour

Cell Formulas
Range	Formula
A2:A7	A2	=A1+1
C1:C7	C1	=IF(AND(WEEKDAY(A1,2)<=5,B1>=TIMEVALUE("08:00"),B1<=TIMEVALUE("17:00")),"Within Hours","Out of Hours")

 

[xxsalahxx]

Hello A2 ais a custom date
01 is the the day
Thank you

 

[xxsalahxx]

What is the actual value in column A? Is this a date formatted as "Dddd dd/mm/yyyy", or is it text typed in just as you've shown? I recommend you use true date values when you are dealing with dates.

If you have real dates then this:

$scratch.xlsm
	A	B	C
1	Monday 01/06/2020	02:14	Out of Hours
2	Tuesday 01/07/2020	08:00	Within Hours
3	Wednesday 01/08/2020	07:59	Out of Hours
4	Thursday 01/09/2020	17:01	Out of Hours
5	Friday 01/10/2020	17:00	Within Hours
6	Saturday 01/11/2020	10:00	Out of Hours
7	Sunday 01/12/2020	16:00	Out of Hours
date hour

Cell Formulas
Range	Formula
A2:A7	A2	=A1+1
C1:C7	C1	=IF(AND(WEEKDAY(A1,2)<=5,B1>=TIMEVALUE("08:00"),B1<=TIMEVALUE("17:00")),"Within Hours","Out of Hours")

 

[xxsalahxx]

What is the actual value in column A? Is this a date formatted as "Dddd dd/mm/yyyy", or is it text typed in just as you've shown? I recommend you use true date values when you are dealing with dates.

If you have real dates then this:

Fantastic this solved it, thank you so much

 

[xxsalahxx]

Is A2 a real date or is it Text?
Is 01 the day or the month?

Thank you got it solved.
Regards

 

[Dave Patton]

Date and Time.xlsm
	A	B	C	D	E
1				08:00	17:00
2	Monday 01-Jun-20	02:14	Out of Hours
3	Monday 01-Jun-20	09:00	Within Hours
4	Monday 01-Jun-20	14:00	Within Hours
5
24c

Cell Formulas
Range	Formula
C2	C2	=IF(AND(WEEKDAY(A2,2)<6,B2>=$D$1,B2<=$E$1),"Within Hours","Out of Hours")
C3	C3	=IF(AND(WEEKDAY(A3,2)<6,B3>=0.333333333333333,B3<=0.708333333333333),"Within Hours","Out of Hours")
C4	C4	=IF(AND(WEEKDAY(A4,2)<6,B4>=8/24,B4<=17/24),"Within Hours","Out of Hours")