Excel help please

Options
Paul24
Paul24 Registered Posts: 578 Epic contributor 🐘
Im trying to generate a variation on what should be a simple IF formula. I have a date deadline column hidden in a spreadsheet, and in the end column I want to cell to read "OVERDUE" if the system date exceeds the deadline date. Cant seem to get it to work.

Any ideas greatly appreciated!

Paul

Comments

  • blobbyh
    blobbyh Registered Posts: 2,415 Beyond epic contributor 🧙‍♂️
    Options
    Enter =today() in cell A1 or somewhere. This will set the system date for you.

    Say you have your hidden due date in cell A4, and you want "Overdue" to appear in cell A5, enter =IF($A$1>A4,"Overdue",""). Essentially, if your system date is greater than your due date, it will insert "Overdue" otherwise it'll leave it blank.

    The dollars in $A$1 mean you can copy this formula to anywhere else on the sheet and it will always reference the system date in A1.

    Alternately, skip the cell in A1 and just type =IF(TODAY()>A4,"Overdue","") in A5 but I personally always like to see the reference cell.
  • Paul24
    Paul24 Registered Posts: 578 Epic contributor 🐘
    Options
    Cheers Rob
  • PGM
    PGM Registered Posts: 1,954 Beyond epic contributor 🧙‍♂️
    Options
    Good formulas Blobby!

    If you work on a system that has a due date of the end of the following month, can that be calculated by excel based on the invoice date? Or would you need to enter it manually?
  • CJC
    CJC Registered Posts: 1,657 Beyond epic contributor 🧙‍♂️
    Options
    Use =EOMONTH(start_date, months).

    If the invoice date was in A2 you'd enter =EOMONTH(A2, 1) to get the end date of the following month.
  • PGM
    PGM Registered Posts: 1,954 Beyond epic contributor 🧙‍♂️
    Options
    CJC wrote: »
    Use =EOMONTH(start_date, months).

    If the invoice date was in A2 you'd enter =EOMONTH(A2, 1) to get the end date of the following month.

    Ah very handy! Thanks.
  • Paul24
    Paul24 Registered Posts: 578 Epic contributor 🐘
    Options
    This is becoming quite a handy little post, I used the LEFT, MID and RIGHT fx the other day for the first time, didint ever know they existed, very useful.
  • Marga
    Marga Registered Posts: 981 Epic contributor 🐘
    Options
    formulae in excel can save you so much time and work somtimes!!!
  • PGM
    PGM Registered Posts: 1,954 Beyond epic contributor 🧙‍♂️
    Options
    Marga wrote: »
    formulae in excel can save you so much time and work somtimes!!!

    They can also create a lot of work. Got to be systematic and tidy with the spreadsheets. Seen a few that have gone haywire and are pumping out all sorts of crazy results.
  • blobbyh
    blobbyh Registered Posts: 2,415 Beyond epic contributor 🧙‍♂️
    Options
    Good practice when designing spreadsheets - especially accountancy based ones that could be used for obtaining investment or firing people - is to ideally use PIT (ProofIng Total) checks that validate the data using different methods where possible with glaring messages that catch your attention when something doesn't add up!

    For example:

    Cells A5 and X1 should theoretically return the same value but have been achieved using different methods to "prove" the data. Thus:

    =IF(A5=X1,"","Error!") combined with conditional formatting (Cell Value Is Equal To "Error!"), only shows Error! in bold red if the cells do NOT equal each other, otherwise leave blank.
  • tigger37
    tigger37 Registered Posts: 200 Dedicated contributor 🦉
    Options
    Excel Help

    Hi Guys -

    As you are doling out excel tips here, can someone please show me how to create a macro? Is there a good website I can look at to give myself more training?
  • sdv
    sdv Registered Posts: 585 Epic contributor 🐘
    Options
    Youtube

    Excellent resources on Excell
  • blobbyh
    blobbyh Registered Posts: 2,415 Beyond epic contributor 🧙‍♂️
    Options
  • tigger37
    tigger37 Registered Posts: 200 Dedicated contributor 🦉
    Options
    Excel Help

    thanks Blobby and SDV :)
  • blobbyh
    blobbyh Registered Posts: 2,415 Beyond epic contributor 🧙‍♂️
    Options
    Macro's are extremely useful for auto-running commonly used routines, especially if you add a nifty macro button as described at the bottom of that webpage. Once you learn how to use (and edit) them, you'll wonder how you ever manged to cope previously!

    I'd advise not to:
    a) Use keyboard shortcuts as these can accidentally run if you press the wrong keys!
    b) Put a save function in the macro, since running the above will kill your sheet before saving it!

    Know the differences between when to save it in a single workbook alone or within your personal macro workbook if you plan to use it on various spreadsheets (such as a quick unlock/lock macro).

    Unlike lookups and other functions, be aware that adding or removing columns in an already operative macro won't automatically update it so you'll need to learn how to manually edit.

    Enjoy experimenting!
Privacy Policy