+ Reply to Thread
Results 1 to 28 of 28

Automatic cell value increase on daily basis but not to consider weekly off days

  1. #1
    Registered User
    Join Date
    03-26-2021
    Location
    California, US
    MS-Off Ver
    MS Office 365
    Posts
    14

    Automatic cell value increase on daily basis but not to consider weekly off days

    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.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,873

    Re: Automatic cell value increase on daily basis but not to consider weekly off days

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,873

    Re: Automatic cell value increase on daily basis but not to consider weekly off days

    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)
    Last edited by AliGW; 03-27-2021 at 04:27 AM.

  4. #4
    Registered User
    Join Date
    03-26-2021
    Location
    California, US
    MS-Off Ver
    MS Office 365
    Posts
    14

    Re: Automatic cell value increase on daily basis but not to consider weekly off days

    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

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,873

    Re: Automatic cell value increase on daily basis but not to consider weekly off days

    What do you think this is doing?

    =(A2/23)*20

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,873

    Re: Automatic cell value increase on daily basis but not to consider weekly off days

    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.

  7. #7
    Registered User
    Join Date
    03-26-2021
    Location
    California, US
    MS-Off Ver
    MS Office 365
    Posts
    14

    Re: Automatic cell value increase on daily basis but not to consider weekly off days

    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 "
    Last edited by major_johnson; 03-27-2021 at 04:55 AM.

  8. #8
    Registered User
    Join Date
    03-26-2021
    Location
    California, US
    MS-Off Ver
    MS Office 365
    Posts
    14

    Re: Automatic cell value increase on daily basis but not to consider weekly off days

    Now, I have added the formula into the below cell of MTD Target.
    Attached Files Attached Files
    Last edited by AliGW; 03-27-2021 at 05:00 AM. Reason: PLEASE don't quote unnecessarily!

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,873

    Re: Automatic cell value increase on daily basis but not to consider weekly off days

    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!
    Last edited by AliGW; 03-27-2021 at 05:10 AM. Reason: Typo

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,873

    Re: Automatic cell value increase on daily basis but not to consider weekly off days

    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)

  11. #11
    Registered User
    Join Date
    03-26-2021
    Location
    California, US
    MS-Off Ver
    MS Office 365
    Posts
    14

    Re: Automatic cell value increase on daily basis but not to consider weekly off days

    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. :-)
    Last edited by AliGW; 03-27-2021 at 05:08 AM. Reason: PLEASE don't quote unnecessarily!

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,873

    Re: Automatic cell value increase on daily basis but not to consider weekly off days

    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?

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    Soft Office 365. Learning Excel all over again!!
    Posts
    29,849

    Re: Automatic cell value increase on daily basis but not to consider weekly off days

    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...
    Attached Files Attached Files
    Glenn



  14. #14
    Registered User
    Join Date
    03-26-2021
    Location
    California, US
    MS-Off Ver
    MS Office 365
    Posts
    14

    Re: Automatic cell value increase on daily basis but not to consider weekly off days

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

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,873

    Re: Automatic cell value increase on daily basis but not to consider weekly off days

    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)

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,873

    Re: Automatic cell value increase on daily basis but not to consider weekly off days

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

  17. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    Soft Office 365. Learning Excel all over again!!
    Posts
    29,849

    Re: Automatic cell value increase on daily basis but not to consider weekly off days

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

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,873

    Re: Automatic cell value increase on daily basis but not to consider weekly off days

    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.

  19. #19
    Registered User
    Join Date
    03-26-2021
    Location
    California, US
    MS-Off Ver
    MS Office 365
    Posts
    14

    Re: Automatic cell value increase on daily basis but not to consider weekly off days

    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.
    Last edited by major_johnson; 04-02-2021 at 12:30 PM.

  20. #20
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,812

    Re: Automatic cell value increase on daily basis but not to consider weekly off days

    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.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  21. #21
    Registered User
    Join Date
    03-26-2021
    Location
    California, US
    MS-Off Ver
    MS Office 365
    Posts
    14

    Re: Automatic cell value increase on daily basis but not to consider weekly off days

    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!

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,873

    Re: Automatic cell value increase on daily basis but not to consider weekly off days

    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.

  23. #23
    Registered User
    Join Date
    03-26-2021
    Location
    California, US
    MS-Off Ver
    MS Office 365
    Posts
    14

    Re: Automatic cell value increase on daily basis but not to consider weekly off days

    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.

  24. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,873

    Re: Automatic cell value increase on daily basis but not to consider weekly off days

    Thanks for the rep. Just one point, though - Im not a man.

  25. #25
    Registered User
    Join Date
    03-26-2021
    Location
    California, US
    MS-Off Ver
    MS Office 365
    Posts
    14

    Re: Automatic cell value increase on daily basis but not to consider weekly off days

    Quote Originally Posted by AliGW View Post
    Im 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.

  26. #26
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,873

    Re: Automatic cell value increase on daily basis but not to consider weekly off days

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

  27. #27
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,812

    Re: Automatic cell value increase on daily basis but not to consider weekly off days

    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.

  28. #28
    Registered User
    Join Date
    03-26-2021
    Location
    California, US
    MS-Off Ver
    MS Office 365
    Posts
    14
    Quote Originally Posted by JeteMc View Post
    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 02-28-2014, 02:31 AM
  2. [SOLVED] Automatic Backup of Excel File on a daily basis
    By Mr.India in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-21-2014, 02:06 AM
  3. Replies: 1
    Last Post: 06-27-2013, 08:12 AM
  4. Replies: 1
    Last Post: 03-21-2013, 10:45 PM
  5. Calculating overtime hours on a daily and weekly basis
    By Skwerl in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-05-2011, 09:12 PM
  6. Increase/decrease amount remaining from original on a daily input basis
    By katie@trainup.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-23-2011, 01:16 AM
  7. Need to sum number of error by weekly and daily basis
    By marc5354 in forum Excel General
    Replies: 2
    Last Post: 11-03-2010, 11:04 AM
  8. Need to count productivity by weekly and daily basis
    By marc5354 in forum Excel General
    Replies: 4
    Last Post: 10-06-2010, 01:36 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1