# Looking up first operating day of month

#### bigbraincantoo03

##### New Member
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
BCDE
2Monthly Operating hours
3NameSamplePlaceSample
4MonthAugust
5Operation Start DateFinal Date1-Feb-00 00:00
6Start Depth#N/AEnd Depth
Calculation
Cell Formulas
RangeFormula
C6C6=VLOOKUP(C5,'Raw Data'!E3:AJ37,4,TRUE)
E5E5=EOMONTH(C5,0)+((1440/60)/24)
E6E6=IF('Raw Data'!\$F\$3:\$F\$1500<=\$E\$5,VLOOKUP(\$E\$5,'Raw Data'!F3:I1500,4,TRUE),"")
Cells with Data Validation
CellAllowCriteria
C4List=\$J\$3:\$J\$15

#### bigbraincantoo03

##### New Member
T202011a.xlsm
ABCDE
1
2
3
4August
5Aug 24, 2020 15:30Sep 1, 2020
6
1e
Cell Formulas
RangeFormula
C5C5=MIN(IF('Raw Data'!E3:E22>(1&C4)+0,'Raw Data'!E3:E22))
E5E5=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.

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Dave Patton

##### Well-known Member
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

##### Well-known Member
To avoid the Array Formula, try the suggestion shown below.

T202011a.xlsm
ABCDE
1
2
3
4August
5Aug 24, 2020 15:30Sep 1, 2020
6
2a
Cell Formulas
RangeFormula
C5C5=AGGREGATE(15,6,1/('Raw Data'!E3:E22>=--(1&C4))*'Raw Data'!E3:E22,1)
E5E5=MIN(MAX('Raw Data'!E3:E25),EDATE(--(1&C4),1))

#### bigbraincantoo03

##### New Member
To avoid the Array Formula, try the suggestion shown below.

T202011a.xlsm
ABCDE
1
2
3
4August
5Aug 24, 2020 15:30Sep 1, 2020
6
2a
Cell Formulas
RangeFormula
C5C5=AGGREGATE(15,6,1/('Raw Data'!E3:E22>=--(1&C4))*'Raw Data'!E3:E22,1)
E5E5=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!

Replies
10
Views
527
Replies
1
Views
71
Replies
3
Views
537
Replies
2
Views
940
Replies
12
Views
710

1,127,269
Messages
5,623,727
Members
415,986
Latest member
C_Braga

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