Number of days between days

J

Joe

How can I get the number an invoice is outstanding when I already have todays
date and the invoice date and I want to get just a number Thanks
 
M

Max

Assuming your invoice dates are running in A2 down

you could place this in say, B2:
=IF(A2="","",IF(TODAY()-A2>30,"Overdue",TODAY()-A2))
and copy B2 down

Col B will return the difference in days or the msg: "Overdue" if the diff
exceeds 30 days. It'll return blanks: "" if there's nothing in col A (eg
empty cells)

If you want a more elaborate text msg for overdue cases with the number of
days included, try instead in B2:
=IF(A2="","",IF(TODAY()-A2>30,"Overdue-"&TEXT(TODAY()-A2,"d")&"
days",TODAY()-A2))
 
S

Sandy Mann

With the Invoice date in B2 then you can simply use:

=TODAY()-B2 formatted as General to give the number of elapsed days and

=IF(TODAY()-B2>30,"Overdue","") to give the warning message.

If you want the number of working days instead of the absolute number of
days then check out the NETWORKDAYS() function.

--
HTH

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
J

Joe

thanks

Max said:
Assuming your invoice dates are running in A2 down

you could place this in say, B2:
=IF(A2="","",IF(TODAY()-A2>30,"Overdue",TODAY()-A2))
and copy B2 down

Col B will return the difference in days or the msg: "Overdue" if the diff
exceeds 30 days. It'll return blanks: "" if there's nothing in col A (eg
empty cells)

If you want a more elaborate text msg for overdue cases with the number of
days included, try instead in B2:
=IF(A2="","",IF(TODAY()-A2>30,"Overdue-"&TEXT(TODAY()-A2,"d")&"
days",TODAY()-A2))
Click to expand...
 
Top