Excel Date Formula - Production Log

iamrobberman

New Member
Joined
Oct 8, 2019
Messages
3
I am finalizing a production log that I have built...I have a Entered Training Date and a Graduation Date listed. I am trying to calculate the number of days instructed within the current month. I have the formula spelled out that gets me exactly what I was looking for, however, this formula does not account for the weekends and federal holidays. How can I modify this formula to get me the current number of days in the month that are instructed (between the two ranges) that accounts for weekends and federal holidays.

current formula is:

=MAX(0,MIN(B3,EOMONTH(TODAY(),0))-MAX(A3-1,EOMONTH(TODAY(),-1)))

The table that lists the 2019 holidays is located in cell T28:T37

The data I am trying to plug in will start in Y3.

Any help is greatly appreciated. (Not sure how to upload an example file to show)
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,793
Office Version
365
Platform
Windows
Hi. If im understanding you want the number of days that appear between those two dates that are in the current month but starting from today? If so try:

=MAX(0,NETWORKDAYS(MAX(A3,TODAY()),MIN(B3,EOMONTH(TODAY(),0)),Holidays))
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,332
Office Version
365
Platform
Windows
Cross posted https://www.excelforum.com/excel-formulas-and-functions/1292270-help-needed-formula-dealing-with-dates-current-month.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

iamrobberman

New Member
Joined
Oct 8, 2019
Messages
3
Hi. If im understanding you want the number of days that appear between those two dates that are in the current month but starting from today? If so try:

=MAX(0,NETWORKDAYS(MAX(A3,TODAY()),MIN(B3,EOMONTH(TODAY(),0)),Holidays))

this does not seem to be working for what I need. I am not as concerned with starting today as I am the entire month...

if we are using October...there are 31 days in October...what I am looking for is to see the Start Date and Graduation date...if any of those dates are in the month of October, I want to know how many of those days are in October, subtract weekends and holidays to give me the actual numbers of days my instructors were teaching. Example is as follow.

Start Date: 23 September 2019
Graduation Date: 10 October 2019

I know that in the month of October, I know the range shows 10 actual days fall in the month of October...what I need is a way to calculate this automatically subtracting weekends and holidays (if the holiday or weekend falls in that range).

Which in this instance, my instructor actually taught 8 days (2 days were on the weekend)...
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,793
Office Version
365
Platform
Windows
Always good to give an example. Worth many words. Try this:

=MAX(0,NETWORKDAYS(MAX(A3,EOMONTH(TODAY(),-1)+1),MIN(B3,EOMONTH(TODAY(),0)),holidays))
 

iamrobberman

New Member
Joined
Oct 8, 2019
Messages
3
This is why I love forums, you all are amazing...does exactly what I am looking for...just to ensure I am calculating correctly...in the formula you provided, I change "holidays" to whatever my table is listed as? Would I need to modify anything when I switch from October production data to November?
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,793
Office Version
365
Platform
Windows
Yes the way ive written it here 'holidays' is a named range but you could just replace it with cell references. Once we reach november the formula will adjust to look for november dates. This produces the first day of current month EOMONTH(TODAY(),-1)+1 and this EOMONTH(TODAY(),0) the last day of current month.
 

Forum statistics

Threads
1,082,359
Messages
5,364,916
Members
400,815
Latest member
Joaquin Phoenix

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top