Excel Date Formula - Production Log

[iamrobberman]

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]

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

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...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]

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]

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]

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]

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.