Excel help please

Paul24

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

blobbyh

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

Cheers Rob

PGM

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

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

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

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

formulae in excel can save you so much time and work somtimes!!!

PGM

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

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

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

Youtube

Excellent resources on Excell

blobbyh

http://www.taltech.com/support/sw_tricks/exmacros.htm

tigger37

Excel Help

thanks Blobby and SDV

blobbyh

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!