Looking up first operating day of month

[bigbraincantoo03]

Hi all,
I'm working on a table that calculates the operating hours every month based on the month selected in a drop down. I tried just using the first day of the month to do subsequent calculations, but those require using the exact start date to come up with a result. I figured an index formula might work in this situation, but I couldn't get the formula to generate the first day of the operation when the month I want is selected from a drop down. I also tried using the min function with combined vlookup conditions, but of course my syntax is likely incorrect and returns #N/A (formula was not good so I did not include). I'm looking at having cell C4 be the drop down month and C5 be the one to show the operations start date/time. Sample table is below.

Sample Data.xlsx
	B	C	D	E
2	Monthly Operating hours
3	Name	Sample	Place	Sample
4	Month	August
5	Operation Start Date		Final Date	1-Feb-00 00:00
6	Start Depth	#N/A	End Depth
Calculation

Cell Formulas
Range	Formula
C6	C6	=VLOOKUP(C5,'Raw Data'!E3:AJ37,4,TRUE)
E5	E5	=EOMONTH(C5,0)+((1440/60)/24)
E6	E6	=IF('Raw Data'!$F$3:$F$1500<=$E$5,VLOOKUP($E$5,'Raw Data'!F3:I1500,4,TRUE),"")

Cells with Data Validation
Cell	Allow	Criteria
C4	List	=$J$3:$J$15

 

[bigbraincantoo03]

T202011a.xlsm
	A	B	C	D	E
1
2
3
4			August
5			Aug 24, 2020 15:30		Sep 1, 2020
6
1e

Cell Formulas
Range	Formula
C5	C5	=MIN(IF('Raw Data'!E3:E22>(1&C4)+0,'Raw Data'!E3:E22))
E5	E5	=MIN(MAX('Raw Data'!E3:E25),EDATE(--(1&C4),1))
Press CTRL+SHIFT+ENTER to enter array formulas.

This is a guess.
You have to remember that we do not know the details of your operation, what you are trying to achieve, etc.

If the above is correct for August, what results do you require for September?

This works great! Thank you for the support. I'm not really sure what the purpose of the '1&' is or why you need to have +0 when inputting the first formula, but I'm assuming it has something to do with how array formulas work (?). I also found that the second formula worked fine without being input as an array formula so hitting 'Enter' worked fine as well for this purpose. Good stuff and thanks again.

 

[Dave Patton]

The =(1&C4)+0 creates a real date Aug 1 2020 the "+0" coerces the text to a number.
I wasn't consistent; I used double negative to coerce the text to a number with the second formula --(1&C4).
Arithmetic operations can also coerce the data to a number.
Yes only the first formula requires array enter CSE.

Your initial post included the times with the dates. This can affect the results. For example August 31 with time is > than just August 31.
I can review the formulas if required.

It can be very useful to mock up a mini example and then review formulas with Excel's Formula Evaluation.

 

[Dave Patton]

To avoid the Array Formula, try the suggestion shown below.

T202011a.xlsm
	A	B	C	D	E
1
2
3
4			August
5			Aug 24, 2020 15:30		Sep 1, 2020
6
2a

Cell Formulas
Range	Formula
C5	C5	=AGGREGATE(15,6,1/('Raw Data'!E3:E22>=--(1&C4))*'Raw Data'!E3:E22,1)
E5	E5	=MIN(MAX('Raw Data'!E3:E25),EDATE(--(1&C4),1))

 

[bigbraincantoo03]

To avoid the Array Formula, try the suggestion shown below.

T202011a.xlsm
	A	B	C	D	E
1
2
3
4			August
5			Aug 24, 2020 15:30		Sep 1, 2020
6
2a

Cell Formulas
Range	Formula
C5	C5	=AGGREGATE(15,6,1/('Raw Data'!E3:E22>=--(1&C4))*'Raw Data'!E3:E22,1)
E5	E5	=MIN(MAX('Raw Data'!E3:E25),EDATE(--(1&C4),1))

This also worked well as an alternative solution. Now I have a separate issue where cells that are referencing these cells turn out as #VALUE, but I'll include that as a separate post so this solution can be saved for everyone. Thanks again Dave!