Formula for Excel to work out a date if given the number of days

[S_Smith]

Hi guys,

I'm currently working on a spreadsheet in Excel 2013 that will allow me to track invoices and when they are due.

Is there a formula I can use that will generate an "invoice due date"?

The information I have in the spreadsheet is as follows (E.G.):

Invoice date, 12/11/2013
Payment terms (Days) 30
Invoice Due Date (can this end date be calculated by Excel?)

Can Excel work out the date the invoice will be due if i give the first day and the number of days it has to be paid by ?

I know I can work this out on my own however it will save me so much time over a working week if Excel can do it for me

Thanks in advance

Smithy

 

[VoG]

Try like this

Excel Workbook
	A	B	C
1	12/11/2013	30	12/12/2013
Sheet1

 

[78chris]

Hello

You can add date and days : then
Invoice date + Payment term
will give you another date (sometimes you have to apply date format , Excel applying number format)

 

[S_Smith]

Hahaha I'm a little embarrassed... I cannot believe it was actually that simple. I thought i would need a big long formula so the dates could be calculated, can you tell I'm an excel beginner??

Thank you guys, very much appreciated

 

[VoG]

To explain, dates in Excel are simply numbers (days since 1 January 1900) displayed in a special format. So you can simply add a number of days to a date to calculate a future date.

 

[S_Smith]

Ok brilliant, thank you again... I have one more question if i'm allowed to ask another question in the same thread?

if the due date has passed and the column next to it - which is "client paid?" still indicates "no" is there anyway of changing the colour in the due date box so it is highlighted and easy for me to see?

Let me know if i need to post another thread on this.

 

[78chris]

Hi again

I have to post agin, something is working wrong on this one

 

[78chris]

Hi again

Yes : you have to use Conditional Formating

Lets say your due dates are in column C and Client Paid date in D, beginning line 2
Select your due dates from C2 to C..

In conditionnal formating, use New Rule, use formulas...
and type
=AND(C2<>"", C2<
<today (),="" d2="No" )[="" quote]
TODAY(), D2="No")
(on one line (here I cannot put it on one single line (bug ???)

then chose the format you want for this case<today (),="" d2="No" )
</today></today>

 

[S_Smith]

Sorry for the late reply, for some reason the website was not letting me post.

Thanks for this.... I can't quite get it to work - it worked and then it wouldn't work when I tried to do the same for the other cells and for some reason now I cannot put into the first cell I managed to do it with.

Ready to throw my laptop (and Excel) out of my office window

 

[happlegate]

I need a formula that delivers due date given a start date and number of days. It will count all days, including holidays and weekends. However, if the due date falls on a holiday or weekend, the soonest future working day should be returned as the due date.

Can you help me with that?

Thanks a bunch!
Heather