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