Adding date depending on the day of the week

Posts
4
Registration date
Friday February 8, 2019
Status
Member
Last seen
February 9, 2019
- - Latest reply: TrowaD
Posts
2525
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 19, 2019
- Feb 12, 2019 at 11:55 AM
Hello,


I want to automatically calculate a date when an invoice is due depending on the day of the week. For example:
the service is provided on a Monday so expenses for the job will be 18 days later and the labour will be on an invoice 25 days later. Tuesday would be 17 days and 24 days etc. invoice date is always a Friday .

If i have the delivery date in A2, the day of the week is B2, expenses date C2 and Labour date D2, what formula do I need to show the relevant dates

Can anyone assist me please

System Configuration: Windows / Chrome 72.0.3626.81
See more 

5 replies

Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
September 20, 2019
1405
0
Thank you
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!

Respond to ac3mark
Posts
4
Registration date
Friday February 8, 2019
Status
Member
Last seen
February 9, 2019
0
Thank you
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
ac3mark
Posts
13031
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
September 20, 2019
1405 -
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.
Respond to dave1961
Posts
4
Registration date
Friday February 8, 2019
Status
Member
Last seen
February 9, 2019
0
Thank you
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
Respond to dave1961
Posts
4
Registration date
Friday February 8, 2019
Status
Member
Last seen
February 9, 2019
0
Thank you
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
Respond to dave1961
Posts
2525
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 19, 2019
365
0
Thank you
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
Respond to TrowaD