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)
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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:
Upvote 0
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.
 
Upvote 0
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)...
 
Upvote 0
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))
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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