+ Reply to Thread
Results 1 to 23 of 23

VBA to calculate no. W/days an output is delayed if today date exceeds due date and status

  1. #1
    Forum Contributor
    Join Date
    10-30-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Office 365
    Posts
    111

    VBA to calculate no. W/days an output is delayed if today date exceeds due date and status

    Good Day,

    I hope this question finds you well.

    I was hoping someone can assist me with the following please.

    I want to know how many days an output is behind/delayed or how many days are still available (to show in column J) compared to its original due date (column D) compared to today's date every time. The date for the day you open the sheet to view.

    This should also be depended on the status in column H, status. If the project is "achieved" within the due date period then the number of days can still be shown in column J to show that an output was delivered ahead of time. If the project is "in progress" the column J w/days can be shown in light blue IF the project is not past its due day. If projects are past its due dates according to column J then an "in progress" the row should be red. Once a project that was red is changed to "achieved" when the output has been completed finally the cell (column J) with the negative delayed number of days should remain in red. This will help me to know that even when projects are now in the "achieve" status and basically completed it will still show me that its due dates where missed.

    The work days should also excluded Saturdays and Sundays and holidays in the sheet.

    I hope this makes sense.

    I have attached an example of how is should look like with some values.

    I thank you in advance.

    Kind Regards,

    Ray
    Attached Files Attached Files
    Last edited by Raylou; 04-29-2020 at 06:48 AM.

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA to calculate no. W/days an output is delayed if today date exceeds due date and st

    Hi Ray

    To get a clear understanding of your requirement, you should upload a sample with a few more lines and manually enter the calculated expected results you want to achieve...

    This way the person who assists is able to test his code against your expected outcome...So I suggest, updating your sample file to display expected results in Column J
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  3. #3
    Forum Contributor
    Join Date
    10-30-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Office 365
    Posts
    111

    Re: VBA to calculate no. W/days an output is delayed if today date exceeds due date and st

    Hi Sintek,

    Thanks for your advice, I have updated the file and the description.

    Thanks!

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA to calculate no. W/days an output is delayed if today date exceeds due date and st

    Okay, lets have a look...
    J2 I understand... 29 - 3 = 26 - 3 P|H = 23 - 8 W|E = 15
    J6 I understand... 29-23 = 6 - 1 P|H = 5 - 2 = 3

    What about the others...
    Last edited by sintek; 04-29-2020 at 08:22 AM.

  5. #5
    Forum Contributor
    Join Date
    10-30-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Office 365
    Posts
    111

    Re: VBA to calculate no. W/days an output is delayed if today date exceeds due date and st

    Yep that is correct.

    For the others like J4 it is from today's date which is now 29 April until to the due date that is 15 May therefore it is 11 days to go to the due date excluding the 1 Public holiday and the two weekends (Saturday & Sunday). The 11 did not count the current date of 29 April in, but it can also to make it the 12. It does not matter to much.

    Hope I understood your question.

  6. #6
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA to calculate no. W/days an output is delayed if today date exceeds due date and st

    Ray...I've made allowance for including start and current date in calculation...This seems more correct...
    have a look and advise...
    Attached Files Attached Files
    Last edited by sintek; 04-29-2020 at 10:11 AM. Reason: Updated post & file

  7. #7
    Forum Contributor
    Join Date
    10-30-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Office 365
    Posts
    111

    Re: VBA to calculate no. W/days an output is delayed if today date exceeds due date and st

    Hi Sintek, thanks that looks almost there

    On the attached Ray v0.2 Once I hit your button to run the vba code I notice that thereafter when adding new rows of outputs the other vba code for the year, week, week start date (columns E, F, G) does not work anymore.

    I see when I add also new rows the number calculated for row 9 & 10 is a negative and not the same as lets say row 3.

    Another question, for example, if I open the sheet tomorrow and it is 30 Apr, will the number of days in column J for row 3, 7, 9, 10 stay the same? Because once the output is marked as "achieved" the number should not change.

    Hope I make sense.
    Attached Files Attached Files

  8. #8
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA to calculate no. W/days an output is delayed if today date exceeds due date and st

    I think it will be best to first isolate the different criteria...

    1. In Progress and overdue
    2. In progress and not overdue
    See red & blue formula calculations in attached Col K-M
    These should be correct?

    Now achieved...How does this work...
    When do you change the status to achieved...on the due date? If so then surely due date gets used and not today date...
    Play around with the formulas in green row to get the accurate results you require...It is just a matter of swapping the two date criteria

    We can code it later...

    Next...
    When must this code run...Every time the workbook opens or when status is changed or both....
    Attached Files Attached Files
    Last edited by sintek; 04-29-2020 at 12:22 PM.

  9. #9
    Forum Contributor
    Join Date
    10-30-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Office 365
    Posts
    111

    Re: VBA to calculate no. W/days an output is delayed if today date exceeds due date and st

    Morning Sintek,

    Thank you. I will have a look at it and revert back to you.

    To answer some of your questions in the meantime. With regards to achieved. When an output is completed the status will be changed to "achieved" it good as well have been "completed". However this does not mean the output was completed on time according to the the due date. So whenever there was a negative number in the "delay/available" (column J) that number should become static and not change ever again. This would be the same for cancelled outputs. Only outputs "in progress" should keep counting etc. Hope it makes sense.

    Yep, I would like this code to run everytime something is also changed in the workbook.

    Will have a look and give you more inputs as well.

    Thanks!

  10. #10
    Forum Contributor
    Join Date
    10-30-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Office 365
    Posts
    111

    Re: VBA to calculate no. W/days an output is delayed if today date exceeds due date and st

    I have added a new file for you to see some of my comments as well. Whenever the status is changed, the other coding for the colour coding and calculations should use the current date of when it was changed.

    The colours for the example rows and days seem correct for the scenarios.

    Thanks!
    Attached Files Attached Files

  11. #11
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA to calculate no. W/days an output is delayed if today date exceeds due date and st

    Prior to the status being changed to Achieved...it will be In-Progress and depending on Date vs Due date 2 different calculations will take place...
    Once you manually change the status to achieved then two different calculations as per above will take place...These results will then be set in stone...

    So code will run on [Workbook Open] to update In-progress week day values and code must run when you change status to Achieved...Correct?

    Also if achieved within due date then green else red

    EDIT...See attached
    I have cleared Day Count and set all status to In-Progress
    On opening the code will run and update colors and day counts...This will happen every time the file is opened...
    On changing status to achieved, the code will run for that row only...
    Attached Files Attached Files
    Last edited by sintek; 04-30-2020 at 04:50 AM.

  12. #12
    Forum Contributor
    Join Date
    10-30-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Office 365
    Posts
    111

    Re: VBA to calculate no. W/days an output is delayed if today date exceeds due date and st

    Morning,

    Yes that sounds about correct yes. That will also be applied to if a project is changed to "cancelled" and "achieved" in the status, the set in stone for the days.

    The code will also run once a new output is added to list with its due date to populate the rest.

    The colour coding yes should be green for the project achieved and within the due date else when achieved but past its due date then the project should be green (because it is now completed) however expect the column J days should be still in red then. I think this is where the negative number can be helpful in die coding. If the number is negative it always should stay red. Same applied for the other scenarios.

  13. #13
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA to calculate no. W/days an output is delayed if today date exceeds due date and st

    See file in Post 11 play around with scenarios and lets perfect one scenario at a time...

  14. #14
    Forum Contributor
    Join Date
    10-30-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Office 365
    Posts
    111

    Re: VBA to calculate no. W/days an output is delayed if today date exceeds due date and st

    Hi Sintek,

    I have opened the sheet and as it is with the in progress scenarios it looks good. When I change it to "achieved" it seems like the code is not running.
    I see it is supposed to call the Updateme but nothing happens. Tried to see if I can see the reason but it seems to be fine.

    I also wanted to add a new row to see if it will then run, but then none of the codes run.

    Am I missing something? Sorry for all the questions and troubles.

  15. #15
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA to calculate no. W/days an output is delayed if today date exceeds due date and st

    On opening the days are calculated...
    when changing Achieved status then if is past due nothing changes as the code has already calculated for that day color remains red...
    When changing Achieved status then if is not past due the days don't change as the code has already calculated for that day - color changes to green though...

    if tomorrow file is opened, achieved remains as is...Nothing changes...


    I also wanted to add a new row to see if it will then run, but then none of the codes run.
    Once you select a status in Column H - I assume In Progress...with the below red snippet change the code will run...

    Please Login or Register  to view this content.

  16. #16
    Forum Contributor
    Join Date
    10-30-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Office 365
    Posts
    111

    Re: VBA to calculate no. W/days an output is delayed if today date exceeds due date and st

    Great I have changed that bit in red and it is great now. I have attached a new file for you with examples. I think then we are about there I guess with the scenarios.

    For row 8 as an example, if I select this project and change the status from "in progress" to "achieved" the project should still change to green except the -5 cell because it was delayed after being achieved. thats where I think the + - for numbers will come into play for the code to keep these numbers in different colour formats.

    For row 13 as an example, if the output is cancelled the row can change into yellow, but once again the Colum J colour will depend on the number in this case it is -20 so it must be in red the cell.

    Stupid question, as mentioned before. If the project is "Achieved" or "cancelled" will the number stop counting already when opening it lets say tomorrow?
    Attached Files Attached Files

  17. #17
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA to calculate no. W/days an output is delayed if today date exceeds due date and st

    K, give this a go...Just been building on to existing code...It can be simplified with Select case Statement but if it ain't broke....
    Attached Files Attached Files

  18. #18
    Forum Contributor
    Join Date
    10-30-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Office 365
    Posts
    111

    Re: VBA to calculate no. W/days an output is delayed if today date exceeds due date and st

    ...then don't fix it....

    Thank I will check it out and see if I can build on it as well.
    Thanks for everything so far.

  19. #19
    Forum Contributor
    Join Date
    10-30-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Office 365
    Posts
    111

    Re: VBA to calculate no. W/days an output is delayed if today date exceeds due date and st

    Wow it looks 90% there.

    I have noticed that the colour coding does not work a hundred percent when changing row that has negative values. All the rows where the numbers are positive in column J change back to the correct colours if the status in column H is changed between the options. However if the numbers in column J is negative it does not change back accordingly. As Per the picture you will see. I think if this is sweet then I can stop abusing your great mind.

    Attachment 675337

  20. #20
    Forum Contributor
    Join Date
    10-30-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Office 365
    Posts
    111

    Re: VBA to calculate no. W/days an output is delayed if today date exceeds due date and st

    I have added two line which seem to fix the problem I had with the lines changing back to the colours. Perhaps you can just check if it is correct in your code.

    Just my last question, can you perhaps point out for me where the code is saying that if an output is marked as "Achieved" it wont change the number anymore for column J when I open the sheet tomorrow or next week? The number should then stay static. That would be great to see and learn.
    Attached Files Attached Files

  21. #21
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA to calculate no. W/days an output is delayed if today date exceeds due date and st

    Column J Color Coding...

    Status In-Progress
    If past due - entire row red
    If not past due - entire row blue
    Status Achieved
    If past due - A:I green J red
    If not past due - entire row Green
    Status Cancelled
    If past due - A:I yellow J red
    If not past due - entire row Yellow

    This should now accommodate...


    EDIT>>>>HAVE NOT LOOKED AT POST 20 FILE


    where the code is saying that if an output is marked as "Achieved" it wont change the number anymore for column J
    For Achieved and Cancelled...
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by sintek; 04-30-2020 at 09:21 AM.

  22. #22
    Forum Contributor
    Join Date
    10-30-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Office 365
    Posts
    111

    Re: VBA to calculate no. W/days an output is delayed if today date exceeds due date and st

    Thank you for showing the code for me where the days are not counted for the when in achieved or cancelled going forward. I will check it tomorrow if all looks fine.

    I added this line to solve the problem of colours changing back as in the file in post 20:

    Please Login or Register  to view this content.
    It seems to work like that because the last file you have send still does not change the colours back when column J is a positive number for "In Progress". Nonetheless, my small contribution hehehe.

    Will let you know if all is well tomorrow.

    Thanks!

  23. #23
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA to calculate no. W/days an output is delayed if today date exceeds due date and st

    still does not change the colours back when column J is a positive number
    Don't get that part....
    Status In-Progress
    If past due - entire row red
    If not past due - entire row blue

    No other requirement...mentioned

    You are referring to testing when you change back and forth...?

    was a small error ... see if this works...
    Attached Files Attached Files
    Last edited by sintek; 04-30-2020 at 10:44 AM.

+ 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. Calculate number of days between invoice date and today's date
    By JHerrick in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-30-2020, 03:53 PM
  2. Replies: 10
    Last Post: 04-07-2016, 03:12 PM
  3. testing if one date exceeds today's date by a specific number of days with vba
    By Dana_Carter in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-08-2016, 07:49 PM
  4. [SOLVED] I need a formula to always calculate exactly 21 days from today's date.
    By phxfitness in forum Excel General
    Replies: 7
    Last Post: 06-26-2015, 07:00 PM
  5. [SOLVED] Calculate 365/180/90/30 days from today date
    By DonW in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-02-2013, 09:44 AM
  6. Replies: 6
    Last Post: 03-04-2010, 03:04 PM
  7. how to calculate a date .. say 34 days from today
    By shaunl in forum Excel General
    Replies: 5
    Last Post: 07-13-2005, 09:38 AM

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