Adding date depending on the day of the week
Closed
dave1961
Posts
4
Registration date
Friday February 8, 2019
Status
Member
Last seen
February 9, 2019
-
Feb 8, 2019 at 11:49 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Feb 12, 2019 at 11:55 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Feb 12, 2019 at 11:55 AM
Related:
- Adding date depending on the day of the week
- Time of day clock stopped - Guide
- Windows calendar show week number - Guide
- Hay day download pc - Download - Simulation
- Microsoft save as pdf ads on - Download - Other
- Adding close friends story to highlights - Instagram Forum
5 responses
Ok, so lets say the date is in D1, and that value is 1/14/2019.
If cell F1, you can add the number of days to the date in D1, like =D2+25
Now in cell G1, you can place a formula that says
=if(TODAY ()>=F2,"Bill it")
That will give you a twenty-five day billing period.
Viola! Now place that example into your model and have a go!
After you have all of that set up, come back and we will format the bill it cells to turn red! Oh what fun!
If cell F1, you can add the number of days to the date in D1, like =D2+25
Now in cell G1, you can place a formula that says
=if(TODAY ()>=F2,"Bill it")
That will give you a twenty-five day billing period.
Viola! Now place that example into your model and have a go!
After you have all of that set up, come back and we will format the bill it cells to turn red! Oh what fun!
dave1961
Posts
4
Registration date
Friday February 8, 2019
Status
Member
Last seen
February 9, 2019
Feb 9, 2019 at 03:16 AM
Feb 9, 2019 at 03:16 AM
Thanks, however it's only to record when payments will be made. If I do the work on a Monday the expenses for the job will be paid 18 days later and the labour 25 days later, if it's carried out on a Tuesday it's 17 & 24 days respectively, wednesday it's 16 & 23, Thursday it's 15 & 22 and finally Friday 14 & 21. Hope that makes it clearer
David
David
Dave, please understand, I gave you a model to apply to your situation. I have spent time authoring complete solutions before, only to have individual cut and paste into their workbook, and report that it didnt work as THEY expected. Then they respond with additiinal specifications and the scope creep begins.
Please take tine to look at my example, and attempt ro scale to your particular worksheet. I do not provide turn key solutions, but attempt to teach.
Give it a try and post back if you get stuck. The info that you gave me will fit into the model, I just did not do all of the biling cycles.
Please take tine to look at my example, and attempt ro scale to your particular worksheet. I do not provide turn key solutions, but attempt to teach.
Give it a try and post back if you get stuck. The info that you gave me will fit into the model, I just did not do all of the biling cycles.
dave1961
Posts
4
Registration date
Friday February 8, 2019
Status
Member
Last seen
February 9, 2019
Feb 9, 2019 at 05:28 PM
Feb 9, 2019 at 05:28 PM
I appreciate what you are saying however, your suggestion doesn't do what I want it to do which is why I gave the information in my second post. I know how to add days to a date. I want to be able to calculate it depending on the day of the week not just the date. I have tried using an IF and I can get it to work with just one day for example:
IF(D3="Monday", D2+18) returns the correct date (D2 is the date the work was completed)
when I nest the IF formula to add an extra date it does not change the end date, I presume I am missing something:
IF(D3="Monday", D2+18, IF D3="Tuesday",D2+17))
I have also tried using an OR but I think I am missing something there too
Dave
IF(D3="Monday", D2+18) returns the correct date (D2 is the date the work was completed)
when I nest the IF formula to add an extra date it does not change the end date, I presume I am missing something:
IF(D3="Monday", D2+18, IF D3="Tuesday",D2+17))
I have also tried using an OR but I think I am missing something there too
Dave
dave1961
Posts
4
Registration date
Friday February 8, 2019
Status
Member
Last seen
February 9, 2019
Feb 9, 2019 at 05:43 PM
Feb 9, 2019 at 05:43 PM
I have worked in my opinion an untidy way of getting the result I want which has a nested IF in a hidden cell to do the calculation for the day of the week and then adding the result to the date (D2). If there is a better way then I appreciate any advice
Didn't find the answer you are looking for?
Ask a question
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Feb 12, 2019 at 11:55 AM
Feb 12, 2019 at 11:55 AM
Hi Dave,
I'm curious to know why IF(D3="Monday", D2+18,IF(D3="Tuesday",D2+17)) doesn't produce the right result when IF(D3="Monday",D2+18) does. It should either change the date from D2 (by adding either 17 or 18) or show 'False' and not just show the date from D2.
Best regards,
Trowa
I'm curious to know why IF(D3="Monday", D2+18,IF(D3="Tuesday",D2+17)) doesn't produce the right result when IF(D3="Monday",D2+18) does. It should either change the date from D2 (by adding either 17 or 18) or show 'False' and not just show the date from D2.
Best regards,
Trowa
