Adding date depending on the day of the weekClosed

Question

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

ac3mark · Answer

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!

dave1961 · Answer

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

dave1961 · Answer

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

dave1961 · Answer

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

TrowaD · Answer

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

Adding date depending on the day of the week

5 responses

Similar discussions

Subscribe To Our Newsletter!