Calculate invoice due date

[studio52]

Hi Guys!

Can someone help me to create a formula for assigning a proper due date to the invoices i am creating?!

I need the invoice to calculate 15 calendar days from the invoice date (including weekends ie WEEKDAYS function cannot be used here) and if the date hits a Saturday then the due date should be the previous day and if Sunday then the next day. So that the due date will never hit the weekend.

Thanks in advance!

[adhawan06]

Hi Studio52,

Assuming you have invoice date in Cell A1 then try

Please Login or Register  to view this content.

I hope it solve your purpose.

[studio52]

Hi Any!

Unfortunately it doesn't work. I have am getting a false message and i cant fin where is the issue.

Attached my file.

[adhawan06]

Hi Studio52,

Sorry I forget to add in last else condition. I also correct above in my last post.

Please Login or Register  to view this content.

[studio52]

Unfortunately this didnt help either.

Please see attached. The weekends are nor avoided.

[adhawan06]

Hi there - the file shared by you is working perfectly fine here. Could you please check once again? It seems that you may have disabled the automatic caculation option and the moment you go to B2 -->F2-->Enter, you will have the non weekend date in front of you. Could you please check once again?

[studio52]

This is very strange as i can see that on your workbook the formula works perfectly, but when i copy the formula and implemented it into the invoice it doesn't work.

Do you know where could be the issue? I checked and the automatic calculation is on.

[adhawan06]

hmmm....Whats your system date format? is it "mm/dd/yy" or "dd/mm/yy". Also can you try-->Put the formula in the cell-->Enter-->Go back to the same cell and just press F2-->Enter See if it works

[studio52]

its dd/mm/yy. My regional settings are Russian. I have tried your suggestion but unfortunately no success

[adhawan06]

It should not impact actually whatever setting you have .

Is it giving any error? Can you do one thing close all workbooks and then open any file and then try again the given function?


Cheers!!!

[studio52]

Tried it now. No error. I am trying to set the invoice date to 17th April and i get the due date shown as 02.05.2015.
Interesting why your workbook is fine but when i copy the formula to a new workbook it doesnt work.

I have attached the original invoice, maybe you will find the difference.

[adhawan06]

Hi There - It will never match because you have changed my formula from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

The formula was not matching with the exact word as you have changed "dddd" to "d" which was returning to 2 instead Saturday which was exactly to be matched the words i have mentioned after = "Saturday" / ="Sunday". Hope it will clear all your doubt.

Use my original formula i have given or refer to the attached sheet.

Hope now there will not be any problem.

Cheers!!!

Anil Dhawan

[studio52]

This was me trying to fix the issue but changing it back to dddd doesn't help. You can try on the workbook i sent that it doesn't work.
The formula is great that you made but there is some difference in the setting im afraid.

[adhawan06]

Hi- I did't made any changes in the attachment earlier have updated the just now. Please refer to the attachment. (I thought you will try at your end by checking it )

[studio52]

Doesnt work. When i change the date to 18th it gives me the due date 03rd may.

[adhawan06]

Hi There - I don't know which cell are you referring to. When I m changing 17 to 18th in the same file, it gives me 4th May Due date.

I also changed in the same book also for your reference, in the second sheet (Sheet3) i have also filled out some Invoice date and against that i have putted my formula for your reference (See Carefully and refer to Sheet 3 Column C & D)

Let me know if still you have any doubt.

[studio52]

Dear Anil,

I have taken now your file sheet 3 and copy pasted your formula to another cell and you can see that i am getting a another date.

[studio52]

And when i go on the cell c2 and press F2 and hit enter the date changes.

[adhawan06]

Hmmm..Issue seems to be there...because the same formula is giving me the correct result and when you using the same it is not giving you exact result.

Last thing, can you try replacing ( , ) with ( ; ) everywhere in the formula?

waiting for your revert

Anil

[adhawan06]

And when i go on the cell c2 and press F2 and hit enter the date changes.

Same is happening here...When i try to hit enter after going into editing mode in Cell E2, it gives expected dates which is there in column c

[studio52]

Tried that also and it didnt help.

[adhawan06]

Tried that also and it didnt help.

Hope you didn't missed any comma

Please Login or Register  to view this content.

[studio52]

Copy paste = didn't work.

[adhawan06]

Hmmm...Let me see something how can i help you....Hmm tell Me what excel and OS are you using? Have you faced any such instance in past also?

I am right now leaving but you can leave message here may be after an hour i will reply your query...But till then i will try to look for some other alternative...

[studio52]

I am using office 2013 on windows 7.
My regional settings are as per attached screenshots.region1.JPGregion2.JPGregion3.JPG

[adhawan06]

Hi there,

Sorry for late reply. I just saw your response, and it was regional setting problem. I have corrected the file and attached for your reference.

Please Login or Register  to view this content.

I have replaced the text function with weekday which will be easy to read to formula.

Hope this time you will get your answer without any doubt.

Cheers!!!

Anil Dhawan

[studio52]

Dear Anil,

Thanks so much for your help! The formula works perfectly now!

[adhawan06]

Glad It Worked finally .


Please take few seconds marking this thread as SOLVED and *Add Reputation If I helped you.



Cheers!!!

Anil Dhawan