Options
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

• 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.
• Registered Posts: 578 Epic contributor ๐
Options
Cheers Rob
• 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?
• 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.
• 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.
• 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.
• Registered Posts: 981 Epic contributor ๐
Options
formulae in excel can save you so much time and work somtimes!!!
• 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.
• 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.
• 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?
• Registered Posts: 585 Epic contributor ๐
Options

Excellent resources on Excell
• Registered Posts: 2,415 Beyond epic contributor ๐งโโ๏ธ
Options
• Registered Posts: 200 Dedicated contributor ๐ฆ
Options
Excel Help

thanks Blobby and SDV
• 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!