Need help formatting dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Excel to track the Accounts Receivables for a small business. I have added a column to track the # of days an invoice is overdue. For example, the invoice date is 7/1/04 due date 8/1/04 It is 2 days overdue. Tomorrow it will be 3 days etc. Right now I am manually calculating these date. Is there a way to format the column to automatically calculate the number of days overdue?
 
Hi Cathy yes

assume due date is in cell A1
and number days overdue in cell C1
then in c1 enter
=today()-A1
then right mouse click on C1
choose format cells
choose number
click OK

Cheers
JulieD


Cathy said:
I am using Excel to track the Accounts Receivables for a small business.
Click to expand...
I have added a column to track the # of days an invoice is overdue. For
example, the invoice date is 7/1/04 due date 8/1/04 It is 2 days overdue.
Tomorrow it will be 3 days etc. Right now I am manually calculating these
date. Is there a way to format the column to automatically calculate the
number of days overdue?
 
Julie,

That did help but I have another question. My invoice due dates are different for each customer. Using your formula, I had to go to each customer and enter it to change the overdue days. Is there any way to get Excel to recognize the different due dates and use 1 formula to format the entire column?
 
Hi Cathy

not understanding ... if you fill down (move your cursor over the bottom
right of the first cell with the formula in it until it becomes a + then
hold down the left mouse button & drag) my formula A1 will change to A2, to
A3 down the page ...

if this doesn't work, can you type out the first three rows of your
worksheet in your reply post with the column & row references.

Cheers
JulieD


Cathy said:
Julie,

That did help but I have another question. My invoice due dates are
Click to expand...
different for each customer. Using your formula, I had to go to each
customer and enter it to change the overdue days. Is there any way to get
Excel to recognize the different due dates and use 1 formula to format the
entire column?
 
When i try to format my dates it doesn't work ? what can i try instead of
having to do it manually ?
 
It sounds like your dates are not really dates, but Text.

Try selecting the dates and choose Data/Text to Columns. Click Next,
Next, then choose "m-d-y" (or the appropriate date form) from the Date
dropdown. Click Finish.

You should then be able to format dates and use the

=IF((TODAY()-A1)>30,TODAY()-A1-30,"")

formula.
 
Back
Top