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)
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,879
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
35,509
Office Version
365
Platform
Windows
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

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,879
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,879
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,089,201
Messages
5,406,808
Members
403,106
Latest member
AliO

This Week's Hot Topics

Top