# Office 365 >  > [SOLVED] Automatic cell value increase on daily basis but not to consider weekly off days

## major_johnson

Here is an example below:

Monday: 1
Tuesday: 2
Wednesday: 3
Thursday: 4
Friday: 5
Saturday: 5
Sunday: 5
Monday: 6
Tuesday: 7

And so on.

I am considering only working days. In a month there are roughly 23 working days on average. Therefore, at end of the month, the total accumulated cell value would be 23.

I calculate based on working days.

Now, instead of writing down the total working days in excel sheet and then calculating what is required makes the work quite boring and lengthy.

I'd like to have the working days accumulated automatically on daily basis but excluding Saturdays and Sundays.

I am actually currently using the below formula:

=TODAY()-DATE(2021,3,1)

I amend this formula to remove the weekends before using it further for the next week.

There should be the option in the formula of having to not only exclude weekends but any day that I would not like to add in the accumulation.

I hope I explained it well.

Please see enclosed as an example of what I would like to achieve.

----------


## AliGW

There are no dates in the attachment. Without any dates at all, what you want to do is not possible.

Something using the WORKDAYS.INTL and/or NETWORKDAYS.INTL functions will work, but we need a more representative (realistic) sample workbook.

----------


## AliGW

So something like this where K1:K5 lists dates to be excluded that are not already Saturdays and Sundays:

=NETWORKDAYS.INTL((EOMONTH(TODAY(),-1)+1),TODAY(),1,K$1:K$5)

=NETWORKDAYS.INTL(_start_date,end_date,weekend_type_1(Sat&Sun),other_dates_to_exclude_)

----------


## major_johnson

in the MTD Target cell there is a formula where I have manually calculated the total working days for the month and counted how many days we have actually worked thereby getting the number of 19 as achievement. This month we worked 20 days excluding Saturdays and Sundays which is what I had to exclude manually. 

The formula above that you have just provided might work if it is somehow inserted there using the idea behind I just explained.

It is do-able and seem not complicated at all, hopefully. :-D

----------


## AliGW

What do you think this is doing?

=(A2/23)*20

----------


## AliGW

Number of working days this month:

=NETWORKDAYS.INTL(eomonth(today(),-1)+1,eomonth(today(),0),1,$K$1:$K$5)

Number of working days so far this month:

=NETWORKDAYS.INTL(eomonth(today(),-1)+1,today(),1,$K$1:$K$5)

You will need to adapt this for what you want. As I said before, there are no dates anywhere at all in the sample file, nor is there any sign of the formula you said you were using.

----------


## major_johnson

This is basically calculating the actual month to date target. If on the 31st of March our total target is 30 then how much is it accumulated for today? Therefore, 30/23 will give us our daily target and then multiplying it by total actual workings day to date that is 20 will give us the total target for 20 days which is 26.

This message is a reply to the below:

" What do you think this is doing:
A2/23*20 "

----------


## major_johnson

Now, I have added the formula into the below cell of MTD Target.

----------


## AliGW

How can your target for 30 March be 30? There are only 23 working days this month when you take out weekends.

Again - you are asking me to compare apples and pears: you talked about a list of dates and shared a formula in your opening post, neither of which appear in the sample workbook you provided.

It's over to you now: I've done all I can with the information you've given. I have shown you how to calculate the total working days in a month and the working days to date - surely you are capable of using these two formulae to get what you want? Have you even tried them yet? You won't learn anything unless you have a go!  :Wink:

----------


## AliGW

OK - as I have already said, you have everything you need in post #6, as far as I can see:

=(A2/23)*(TODAY()-DATE(2021,3,1)-6)

=(A2/NETWORKDAYS.INTL(eomonth(today(),-1)+1,eomonth(today(),0),1,$K$1:$K$5))*NETWORKDAYS.INTL(eomonth(today(),-1)+1,today(),1,$K$1:$K$5)

----------


## major_johnson

And I sincerely appreciated every bit of it. It's just the formulas are too vague and complicated for me to even understand. But anyways, I will spend some hours understanding and then try to use these. 

Furthermore to your concern regarding target, it's a monthly target. It has nothing to with days. It might even be 50. It is up to a company how to achieve it. :-)

----------


## AliGW

Sorry - based on your last sentence, I have no idea what you are trying to do!

What is vague about post #6? I have told you what the formulae do. YOU have to take them, try them, use evaluate formula on the formulas ribbon to work out what's happening. I even explained the syntax to you in post #3 - how much more detail do you need?

----------


## Glenn Kennedy

This is a bit confused, to say the least!!

=NETWORKDAYS.INTL(EOMONTH(TODAY(),-1)+1,EOMONTH(TODAY(),0),"0000011",$L$1:$L$6)

will give you the EXACT number of working days in the current month.  It excludes saturday and Sunday as weekends.  If you wish to exclude specific individual days, add them in L1 to L6.  If you systematically wish to exclude (for example) Wednesdays, that can be done, too, with a simple formula tweak.  It was unclear from your description what you wanted to do...

----------


## major_johnson

Thank you very much for your time and patience. :-)

----------


## AliGW

Glenn's formula does the same as mine from post #6:

=NETWORKDAYS.INTL(eomonth(today(),-1)+1,eomonth(today(),0),1,$K$1:$K$5)

----------


## AliGW

Thanks for the rep - do we take it you have now worked it out?

----------


## Glenn Kennedy

I didn't read my way through your string of messages.

----------


## AliGW

OK. Well, thanks for that. Not really sure what to take from it - I guess you're telling me that I have confused the issue, but I'm not quite sure how.  :Confused:

----------


## major_johnson

Hello, friends! :-D

I was busy because it was closing of the month.

Now, I had quick look at the both of your work and I concluded that it was not really what I needed. :-(

Yes, I am sorry my way of explanation is not well.

Let me try again. :-D

The formulas used are excluding weekends, which is what I need. Thank for you that.

But here is the thing. The formula should be automatic. It should workout on daily basis. It will accumulate working days into one cell on daily basis. When it reaches to a day off, it will stop there until it reaches another working day. 

Both of the formulas provided are showing for the total month. It should show this total value at end of the month not today. For today, it should show how many working days are for today. 

It has nothing to do with my excel file. It's simple logic. This new month started. Today is the second working day. It should show only 2 in a cell! When I open the excel file tomorrow, the cell value would still be 2 because Saturday is not a working day! For Sunday it would be 2 again! But if I open the file on Monday, the cell value would be 3! Simple!

I hope I have explained well. I really need this. :-(

Please forget everything, one cell should calculate the working days of a month on daily basis. Is that possible?

Mr. Glenn, you understand my point as I have reviewed your formula. But your formula is showing already 22 for this month. It should be only 2 for today. On Monday, it will be 3. And so on.

Mr. Ali, your formula is showing 22 too. It should show 2 for today. Thursday and Friday are working days. When I open the file back on Monday the cell would be updated to 3. And on Tuesday to 4. At end of the month, when I open the file, the cell would have automatically calculated to 22 of total working days. But for daily basis, it should calculate on daily working days whenever I open the file the cell would update values itself.

----------


## JeteMc

> When I open the file back on Monday the cell would be updated to 3. And on Tuesday to 4.



The second formula in AliGW's post #6, displayed below, yields 4 as requested.
=NETWORKDAYS.INTL(eomonth(today(),-1)+1,today(),1,$K$1:$K$5)
Let us know if you have any questions.

----------


## major_johnson

Yes! Perfect! Thanks!

Now, that things are aligned, I would like to dig a bit further deep.

Question#1
How would I now include Saturdays and Sundays as workdays but only Fridays as weekend? This would mean, six working days and one off per week, that is the Friday.

Question#2
Similar to the above, suppose there are holidays in a month and I would like to consider any date as an off, pre-planned or in advance for the month, how would I do that?

For example, the month started, and I know for a fact that on the 19th of April the office will be closed. I want the formula to be pre-adjusted in the beginning of the month for the holiday. Once the days reach to the mentioned date, it will automatically not consider it as working day.

Please if you could kindly explain it to me how to add and remove days, that would be much appreciated. :-)

I am really grateful to you for going through all the messages and understood my ignorant way of explanation!

I would like to apologize to everyone for wasting much of their time for a simple task as such.

I am patiently waiting for your reply Mr. JeteMc.

Thank you again!

----------


## AliGW

Q1:

=NETWORKDAYS.INTL(eomonth(today(),-1)+1,today(),"0000100",$K$1:$K$5)

Q2:

Add any further holiday dates to the range in K - just extend the range in the formula to cover all holiday dates.

----------


## major_johnson

Oh, man, I can finally die in peace now!

I love all and each one of you for this!

Everything is working!

Really appreciate it.  :Smilie:  :Smilie:  :Smilie:

----------


## AliGW

Thanks for the rep. Just one point, though - Im not a man.  :Wink:

----------


## major_johnson

> Im not a man.



My sincerest apologies! I saw your picture and concluded you as a female but then your name is AliGW. Ali is an Arabic male name.

----------


## AliGW

I am neither, but Ali is my name and the picture is of me (taken when I was teaching online from my garden last summer during lockdown).  :Smilie:

----------


## JeteMc

Although I only had a small role in this I wanted to say You're Welcome and to Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

----------


## major_johnson

> Although I only had a small role .



Are you kidding me? 

For me, you were the whole reason it got solved. I was left stranded. It would have taken me ages to get there or even understand. 

I am not saying the others didn't help, of course they solved it, especially, Ms. AliGW, but you were the one who guided me to the solution and especially how it can be solved.

Thank you again! Stay safe and blessed! Take care!

----------

