Excel help please
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
Any ideas greatly appreciated!
Paul
0
Comments
-
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.0 -
Cheers Rob0
-
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?0 -
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.0 -
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.0
-
formulae in excel can save you so much time and work somtimes!!!0
-
-
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.0 -
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?0 -
Youtube
Excellent resources on Excell0 -
-
Excel Help
thanks Blobby and SDV0 -
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!0
Categories
- All Categories
- 1.2K Books to buy and sell
- 2.3K General discussion
- 12.5K For AAT students
- 325 NEW! Qualifications 2022
- 160 General Qualifications 2022 discussion
- 11 AAT Level 2 Certificate in Accounting
- 56 AAT Level 3 Diploma in Accounting
- 95 AAT Level 4 Diploma in Professional Accounting
- 8.9K For accounting professionals
- 23 coronavirus (Covid-19)
- 273 VAT
- 92 Software
- 274 Tax
- 138 Bookkeeping
- 7.2K General accounting discussion
- 202 AAT member discussion
- 3.8K For everyone
- 38 AAT news and announcements
- 345 Feedback for AAT
- 2.8K Chat and off-topic discussion
- 582 Job postings
- 16 Who can benefit from AAT?
- 36 Where can AAT take me?
- 42 Getting started with AAT
- 26 Finding an AAT training provider
- 48 Distance learning and other ways to study AAT
- 25 Apprenticeships
- 66 AAT membership