bigbraincantoo03
New Member
 Joined
 Nov 2, 2020
 Messages
 10
 Office Version

 2016
 Platform

 Windows
 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.
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  1Feb00 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 