Date minus a date formula

[Sharr76]

Hi,
I am trying to add a formula that will give me the amount of days overdue by subtracting two dates:

example

Column D3 has 20/02/2008
Column D4 has 20/02/2009

how can I formulate a sum to give me the number of days overdue?

How can I formulate a sum to then give me the number of plus days?

Thanks

[sweep]

Hi,

How about this:

=IF(D4>D3,DATEDIF(D3,D4,"d")&" Overdue",DATEDIF(D4,D3,"d")&" Plus days")

[DonkeyOte]

Another alternative:

=ABS(D4-D3)&IF(D4>D3," Days Overdue"," Plus Days")

[Sharr76]

Hi, thanks for that its great but I now would like to know how many days too the next due date.

I have attached an example.

Its the #NUM! bit I would lit to show how many days until the next due date.

Thanks all

[DonkeyOte]

Post an XL file - not a PDF.

[Sharr76]

Ok I changed the file type..

Thanks

[DonkeyOte]

the formula you have entered is incorrect... ie your first IF criteria is referencing incorrect cells, you should seemingly be comparing columns (A to B) not rows (1 to 2)

[DonkeyOte]

DATEDIF is available up to XL2007 AFAIK but it is not listed as available or shown in Help etc... I would be surprised if MS were to deprecate altogether given issues it would cause.

[Sharr76]

the formula you have entered is incorrect... ie your first IF criteria is referencing incorrect cells, you should seemingly be comparing columns (A to B) not rows (1 to 2)

Im using this formula: =IF(A2>A1,DATEDIF(A1,B1,"d")&" Days Overdue",DATEDIF(B1,A1,"d")&" Plus days")

Which is right, the attachment has been cut down to three columns

[DonkeyOte]

Really, A2 > A1 is correct ? Are you sure A2 shouldn't in fact be B1 ... ?

[Sharr76]

Really, A2 > A1 is correct ? Are you sure A2 shouldn't in fact be B1 ... ?

Well now you got me really confused I cant get it to work at all now....

[DonkeyOte]

Well, either:

=IF(B1>A1,DATEDIF(A1,B1,"d")&" Days Overdue",DATEDIF(B1,A1,"d")&" Plus days")

or

=ABS(B1-A1)&IF(B1>A1," Days Overdue"," Plus Days")

[Sharr76]

Well, either:

=IF(B1>A1,DATEDIF(A1,B1,"d")&" Days Overdue",DATEDIF(B1,A1,"d")&" Plus days")

or

=ABS(B1-A1)&IF(B1>A1," Days Overdue"," Plus Days")

Ok thanks for that, its works fine, but its not showing the plus days, I have attached a proper example.

Lines one and three are showing as overdue, when they should be Plus Days

[DonkeyOte]

You will need to explain the logic as to why they should be showing as Plus Days given the dates in B exceed the dates in A in all rows -- ie all would appear to be Overdue.

[DonkeyOte]

If based on arbitrary entry in G then:

=ABS(E1-D1)&IF(G1="Overdue"," Days Overdue"," Plus Days")

[Sharr76]

You will need to explain the logic as to why they should be showing as Plus Days given the dates in B exceed the dates in A in all rows -- ie all would appear to be Overdue.

Well I was hoping that there was a formula that would do both.....the reason why I want to show this is so as the column will update itself if the right forumla is in it...this document is available to some employees. I would like a formula in that section to show something..... if its possible...

[DonkeyOte]

I'm afraid I'm completely lost - I don't see how if all dates in B are greater than the dates in A some should be seen by XL as being Overdue and others not, there is no discernible logic.