+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    10-31-2012
    Location
    Bracknell
    MS-Off Ver
    Excel 2010
    Posts
    2

    Smile 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
    Last edited by Prydien; 11-04-2012 at 01:20 PM.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: New to Excel bit off more than I can chew

    Untested

    if(and(f4<>0;a4>60),"paid late";if(f4<>0,"paid",if(a4>60,"overdue";a4<60,"due")))
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: New to Excel bit off more than I can chew

    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
    Last edited by dredwolf; 10-31-2012 at 06:49 PM. Reason: forgot to add the "paid" result

  4. #4
    Forum Contributor BeachRock's Avatar
    Join Date
    11-01-2011
    Location
    Oregon, United States
    MS-Off Ver
    Excel 2016
    Posts
    401

    Re: New to Excel bit off more than I can chew

    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.
    Last edited by BeachRock; 11-01-2012 at 08:45 AM. Reason: Slight change to G4 formula for nothing in A4
    -------------
    Tony

  5. #5
    Registered User
    Join Date
    10-31-2012
    Location
    Bracknell
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: New to Excel bit off more than I can chew

    Thanks guys thats a great help. Got some great pointers there to set me on my way. (despite the apparently incomprehensible thread title)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1