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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,634
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Try the following for the first Workday of August 2020.
If this does not help, please describe what you require.
N.B. My system's Regional Settings are International.
If the formulas do not work and your system is USA based, reverse the Month and the 1.


T202011a.xlsm
ABCDE
1
2
3
4AugustFirst Day
5Mon Aug 3, 2020Aug 1, 2020
1e
Cell Formulas
RangeFormula
C5C5=WORKDAY(--(1&C4)-1,1)
E5E5=--(1&C4)
 
Last edited:

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643
Dave's formula can be shortened even further: =WORKDAY((1&C4)-1,1)
And no need to put the the Month in front of the 1 for the US.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,634
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Thanks Tetra201

Your suggestion also works on my system.

The "-1" coerces the text to a number (the date).
 

bigbraincantoo03

New Member
Joined
Nov 2, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Try the following for the first Workday of August 2020.
If this does not help, please describe what you require.
N.B. My system's Regional Settings are International.
If the formulas do not work and your system is USA based, reverse the Month and the 1.


T202011a.xlsm
ABCDE
1
2
3
4AugustFirst Day
5Mon Aug 3, 2020Aug 1, 2020
1e
Cell Formulas
RangeFormula
C5C5=WORKDAY(--(1&C4)-1,1)
E5E5=--(1&C4)
Thanks for the suggestion! I don't think I explained my problem correctly though.

What I'm looking for is a reference for the first day we started doing work in the month. In the example sheet I have, the work started August 24th so I wanted to reference the project start date based on selecting the month in the drop down. In this example I wanted to be able to select August from the drop down and have the formula show the date '24 Aug 20 15:30'. The formatting is easy since I can always format it as a text, but the tricky part is showing the first day the operation started since sometimes we start in the middle of the month.

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


Here's the output table and the raw data where it's supposed to be taking the information from is below. I hope this clears things up and sorry for the confusion.

Sample Data.xlsx
ABCDEFGHIJKLMN
2NO.HOLECASENAMESTART DATEEND DATEOP HOURSSTART DEPTHEND DEPTHFLUIDWEIGHTOPERATIONRESP. CO.PHASE
3024-Aug-20 15:3024-Aug-20 18:303.0065Start of operation
4124-Aug-20 18:3025-Aug-20 00:005.56565
5225-Aug-20 00:0025-Aug-20 01:001.06565
6325-Aug-20 01:0025-Aug-20 02:301.565150
7425-Aug-20 02:3025-Aug-20 03:301.0150150
8525-Aug-20 03:3025-Aug-20 05:302.0150150
9625-Aug-20 05:3025-Aug-20 07:302.0150150
Raw Data
Cell Formulas
RangeFormula
F3:F9F3=E3+(G3/24)
E4:E9E4=F3
A5:A9A5=IF(G5>0,A4+1,A4)
H4:H9H4=IF(I3>0,I3,"")
Cells with Data Validation
CellAllowCriteria
M4:M9List=Compania
B3:B37List=Agujero
C3:C37List=Tam_TR
D3:D37List=Pozo
N3:N9List=Fase
J4:J9List='C:\Users\cantjos01\Desktop\[Grafica de Avance - V3.2.xlsx]Fluidos'!#REF!
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,634
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
consider the array formula that follows
does it yield the required information?

T202011a.xlsm
C
4August
5Aug 24, 2020 15:30
6
1e
Cell Formulas
RangeFormula
C5C5=MIN(IF('Raw Data'!E2:E10000>(1&C4)-1,'Raw Data'!E2:E1000))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

bigbraincantoo03

New Member
Joined
Nov 2, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

consider the array formula that follows
does it yield the required information?

T202011a.xlsm
C
4August
5Aug 24, 2020 15:30
6
1e
Cell Formulas
RangeFormula
C5C5=MIN(IF('Raw Data'!E2:E10000>(1&C4)-1,'Raw Data'!E2:E1000))
Press CTRL+SHIFT+ENTER to enter array formulas.
Thanks Dave. This helps, but only gets me the minimum value of the column. What I was trying to do was get the date range to change when a different month is selected in the in drop down list (cell C4) above the 'Operation Start Date' row. In this situation, I wanted to populate the max and min values for the month selected (24-Aug-20 15:30 should be the operation start date in C5, and 1-Sep-20 00:00 should be the end date in E5).
This should also change when I select 'September' from the drop down to 1-Sep-20 00:00 (start) and 25 Sep-20 01:30 (end). I see now that I also need to change the formula for the final date as this only yields the date from the end of the month. I'm not sure if this makes sense, but please let me know if I need to clarify further.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,634
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
We need a clearer example and explanation before we can suggest another formula.
 

bigbraincantoo03

New Member
Joined
Nov 2, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
We need a clearer example and explanation before we can suggest another formula.
Ok, let me try this.
Sample Data.xlsx
BCDE
2Monthly Operating hours
3NameSamplePlaceSample
4MonthSeptember
5Operation Start Date24-Aug-20 15:30Final Date1-Sep-20 00:00
6Start Depth0End Depth1,626
Calculation
Cell Formulas
RangeFormula
C5C5=MIN('Raw Data'!E3:E314>(1&C4)-1,'Raw Data'!E3:E314)
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),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Data Validation
CellAllowCriteria
C4List=$J$3:$J$15


Cell C4 is the drop down for the month. When the month is selected, the first available date is populated in C5. The 'Final Date' cell (E5) should show either the last available operating date or midnight of the first day of the next month. I'm only including dates in the sample table below so hopefully this helps clarify what I mean.

Cell Formulas
RangeFormula
F3:F25F3=E3+(G3/24)
E4:E25E4=F3
A5:A25A5=IF(G5>0,A4+1,A4)
H4:H25H4=IF(I3>0,I3,"")
O3:O25O3=IF(N3="Perforación",CONCATENATE("P_",B3),IF(N3="Plana",CONCATENATE("CE_",C3),""))
Cells with Data Validation
CellAllowCriteria
M4:M25List=Compania
P3:P25List=INDIRECT($P3)
B3:B37List=Agujero
C3:C37List=Tam_TR
D3:D37List=Pozo
N3:N25List=Fase
J4:J25List='C:\Users\cantjos01\Desktop\[Grafica de Avance - V3.2.xlsx]Fluidos'!#REF!
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,634
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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?
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,128,117
Messages
5,628,790
Members
416,340
Latest member
PJB1102

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