Date Formula
 

Hi

I am trying to work out a formula that will tell me the number of days
between two dates. For example, if I have a column that contains one set of
dates and another column that contains a set of dates - is there a formula I
can use to see the number of days difference between the two dates. I need
to be able to see whether a customer is 30 60 or 90 days overdue with payment.

Hope that is clear

Many thanks

--
Kind regards

Ann Shaw

Date Formula
 

in C1

=IF(B1-A1<=30,"<=30 days overdue",IF(AND(B1-A130,B1-
A1<=60),"30<overdue<=60",IF(AND(B1-A160,B1-
A1<=90),"60<overdue<=90","90 days overdue"))))

copy down, then apply autofilter

Date Formula
 

I am putting in the formula exactly as suggested but keep getting #VALUE! in
the cell. I have checked it many times and it is the correct formula.


--
Kind regards

Ann Shaw


"Jarek Kujawa" wrote:

in C1

=IF(B1-A1<=30,"<=30 days overdue",IF(AND(B1-A130,B1-
A1<=60),"30<overdue<=60",IF(AND(B1-A160,B1-
A1<=90),"60<overdue<=90","90 days overdue"))))

copy down, then apply autofilter

Date Formula
 

Just an alternative:

=IF(COUNT(A1:B1)<2,"",CHOOSE(MIN(CEILING(B1-A1,30)/30,3),"Up to 30 days
overdue","31 - 60 days overdue","Over 60 days overdue"))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Annie" wrote in message
...
Hi

I am trying to work out a formula that will tell me the number of days
between two dates. For example, if I have a column that contains one set
of
dates and another column that contains a set of dates - is there a formula
I
can use to see the number of days difference between the two dates. I
need
to be able to see whether a customer is 30 60 or 90 days overdue with
payment.

Hope that is clear

Many thanks

--
Kind regards

Ann Shaw

Date Formula
 

It sounds like the *dates* you have are really text representations of dates
not real dates. Try pretending to reformat one of the cells to General and
it should show a value like 39493 in the Format cells dialog box. If it
still shows the date then it means that it is text.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Annie" wrote in message
...
I am putting in the formula exactly as suggested but keep getting #VALUE!
in
the cell. I have checked it many times and it is the correct formula.


--
Kind regards

Ann Shaw


"Jarek Kujawa" wrote:

in C1

=IF(B1-A1<=30,"<=30 days overdue",IF(AND(B1-A130,B1-
A1<=60),"30<overdue<=60",IF(AND(B1-A160,B1-
A1<=90),"60<overdue<=90","90 days overdue"))))

copy down, then apply autofilter

Date Formula
 

Thanks Jarek

I got it to work!!


--
Kind regards

Ann Shaw


"Jarek Kujawa" wrote:

in C1

=IF(B1-A1<=30,"<=30 days overdue",IF(AND(B1-A130,B1-
A1<=60),"30<overdue<=60",IF(AND(B1-A160,B1-
A1<=90),"60<overdue<=90","90 days overdue"))))

copy down, then apply autofilter

Date Formula
 

Thanks Sandy Mann but I did have date format in - I had made an error in the
parentheses - it's now working fine.
--
Kind regards

Ann Shaw


"Sandy Mann" wrote:

It sounds like the *dates* you have are really text representations of dates
not real dates. Try pretending to reformat one of the cells to General and
it should show a value like 39493 in the Format cells dialog box. If it
still shows the date then it means that it is text.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Annie" wrote in message
...
I am putting in the formula exactly as suggested but keep getting #VALUE!
in
the cell. I have checked it many times and it is the correct formula.


--
Kind regards

Ann Shaw


"Jarek Kujawa" wrote:

in C1

=IF(B1-A1<=30,"<=30 days overdue",IF(AND(B1-A130,B1-
A1<=60),"30<overdue<=60",IF(AND(B1-A160,B1-
A1<=90),"60<overdue<=90","90 days overdue"))))

copy down, then apply autofilter