Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
Excel Formula to calulate number of days passed from date to date | Excel Discussion (Misc queries) | |||
Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
formula to calculate future date from date in cell plus days | Excel Worksheet Functions |