A4 date of invoice - E4 invoice status - F4 Date invoice was paid - G4 number of days invo

Hello
I'm new to Excel and this forum, it seems like a great place to get help.
I just don't know enough about Excel yet to be able to do an informed search so apolgies if this is a common question.

I've started on a spreadsheet (my first ever) to track invoices and report their status eg "paid", "overdue" etc.
I've got the hang of conditional formatting and AutoSum, I just hit a bit of a wall with the status reporting.
Here is what I'm trying to achieve:

A4 date of invoice - E4 invoice status - F4 Date invoice was paid - G4 number of days invoice is overdue.

I will be manualy entering dates in A4 and F4

If any date is entered in F4 then automaticaly enter the text "paid" in E4 and ignore any other formula.

If date entered in A4 is older than 60 days and F4 is empty then automaticaly enter the text "overdue" in E4 and enter number of days over 60 in G4.

If the date entered in A4 is younger than 60 days and F4 is empty then automaticay enter the text "due" in E4.

If the date entered in F4 is older than 60 days from the date in A4 then automaticaly enter the text "paid late" in E4.


I need this to carry on down each column.

I hope i've expained it ok. Looking forward to your replys thanks

Untested

if(and(f4<>0;a4>60),"paid late";if(f4<>0,"paid",if(a4>60,"overdue";a4<60,"due")))

maybe this :

(cell E4)= IF(CELL("type",A4)="v",IF((TODAY()-A4)<60,IF(ISBLANK(F4),"due","paid"),IF(ISBLANK(F4),"overdue","paid late")))

this formula assumes that proper error checking has been done on the dates in row A and F
to extend, just copy down the copy and paste down the e column

also not tested

Hi Prydien,

Put this formula into your E4 cell: =IF(F4>0,"Paid",IF(AND(A4<TODAY()-60,F4=""),"Overdue"))

Copy and paste formula down.

Put this formula into your G4 cell: =IF(A4="","",IF(A4<TODAY()-60,TODAY()-A4))

Copy and paste formula down.



EDIT: I did test mine and it seems to work fine.