08 6210 8500
Get on Excel's case Case sensitive functions By Neale Blackwood If you need to change the case of text, Excel has three functions that can assist. The functions are UPPER, LOWER and PROPER. Examples of the output are shown below. The formulas assume cell A1 contains john SMITH and A2 contains 30/6/09. The TEXT [...]
Week Numbers The WEEKNUM function By Neale Blackwood If you use week numbers and you need to know which week in the calendar year a specific date is in, you can use the WEEKNUM function. This function allows you to specify whether your week starts on a Sunday or a Monday. If cell A1 [...]
How many months between two dates MONTH functions Did you know we have a YouTube channel with weekly videos dedicated to Excel, Power Query and Power BI? By Neale Blackwood How many months? If you need to calculate the number of whole months between two dates there is a formula that can [...]
SUM positives SUM positives or negatives only By Neale Blackwood, 30 Apr 15 If you need to SUM only the positive numbers in a range then use the SUMIF function. If the range is A1:A1000 then the following formula will SUM only the positive numbers. =SUMIF(A1:A1000,">0") This following formula will SUM all the negative [...]
Multiple Password Control Show and hide sheets based on different passwords By Wyn Hopkins Often we have spreadsheets where we'd like different people access to different sheets. One way of doing that is demonstrated in the linked Excel File. The approach uses a combination of an Excel Table with some VB Code. [...]
Converting Dates from mm/dd/yyyy to dd/mm/yyyy By Jeff Robson Sometimes you get a spreadsheet or text file that has US format dates i.e. mm/dd/yyyy As our UK/Australian date format is dd/mm/yyyy, you end up with some dates that Excel recognises (e.g. where the day of the month is 12 or less) and some [...]
3 Ways to Calculate Time By Jonathan Lim Let's say it is 8:00:00 am now, and I want Excel to calculate the time 1 hour, 1 minute and 1 second earlier or later. There are three methods by which this can be done: 1. The Long Way Using a constant of the hour, minute [...]
Life’s no drag with Excel – or is it? By Wyn Hopkins You may be familiar with a LEFT-CLICK drag to copy down a formula but we bet you’ve never tried a RIGHT-CLICK drag, here’s what happens….. It's fairly common knowledge that you can fill a list of dates by typing in one [...]
What's Up Glitches? By Wyn Hopkins Here's an interesting glitch pointed out to me by an attendee on our Excel Super User Course. A simple SUMIFS formula can go very wrong unintentionally when sorting results. In the first example (blue) I've clicked on another sheet and then clicked back into this sheet to get [...]
INDEX with Approximate MATCH By Jeff Robson Technical Difficulty: Advanced Most people think of using INDEX with only an exact MATCH (i.e. a match type of zero). Here's an example of where you need something more flexible. Pricing Bands Mixed with Flat Rates Our client had received freight prices from their supplier [...]
How to Manage Long Formulas How long is too long? By Jeff Robson There's a general rule of thumb when it comes to Excel formulas: your formulas should be no longer than your thumb! When you're designing a spreadsheet, that's what you should aim for. But sometimes, you have to break this rule: To [...]
Days in Financial Year The DATE function By Neale Blackwood As a follow up to the tip on calculating the number of days into the calendar year, here is a formula to calculate the number of days into the 30 June financial year. =IF(MONTH(A1)<7,A1-DATE(YEAR(A1)-1,6,30),A1-DATE(YEAR(A1),6,30)) First published: CPA AUSTRALIA Website 2009
Reach out to the Access Analytic team Monday to Friday on (08) 6210 8500 or submit your contact information and we will be in touch.
Get Ahead of the Game – Book Your Training Now!
Wyn has been awarded MVP status by Microsoft every year since 2017 and is the author of the book “Power BI for the Excel Analyst”. He is known worldwide for sharing his knowledge via the Access Analytic YouTube channel with over 5 million views and 90,000+ subscribers.
He gained his Chartered Accountant qualification at PwC in 2000 and has extensive Business Analyst experience with HBOS and Barclays in the UK plus multinational clients in Perth.
Wyn is skilled in Excel and Power BI development, Power Query automation, management reporting and is an experienced Excel and Power Bl trainer.
Pay my invoice
