Looking up first operating day of month

bigbraincantoo03

New Member
Joined
Nov 2, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
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
Joined
Nov 2, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
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.
 

Some videos you may like

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
Joined
Feb 15, 2002
Messages
4,626
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Feb 15, 2002
Messages
4,626
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Nov 2, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
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!
 

Watch MrExcel Video

Forum statistics

Threads
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.
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