I had a question during one of my Date and Times Webinars in February. It was about months and fortnights. I couldn’t answer it during the webinar, but I did follow up with an email with the solution. The answer follows.
I had a question on another post on how to convert Nov 21, 2014 into a date Excel recognises. The solution involves six functions working together.
It is common to use Q1 for quarter one. Excel will even cycle through Q1,Q2,Q3 and Q4 when you drag a cell contain Q1. What if you want to use the sequence M1 to M12 for months? Custom Lists to the rescue!
Excel has a few issues with dates. Mainly they relate to the dates that are imported into Excel. Different systems present different problems. This post solves an issue with dates that include times.
You can create a macro to open a CSV file. One problem you may face is that dates are treated as US dates. A simple change can fix this.
Excel has had a week number function for many versions, but Excel 2013 added a new week number function that complies with the ISO 8601 standard week number.
When working with loans or leases, it is common to have to add a number of years to a start date to determine the end date. An Excel function can automate that process.
If you want to place ordinals (st, nd, rd and th) after a number in a date. You can use the CHOOSE function to do this.
Date data imported from other systems can include times. This can make lookup and other calculations difficult. One function can make removing or extracting time easy.
When you use date grouping (by months) in one Pivot Table report it affects other Pivot Table’s date reporting from the same data. There is a work around to allow you to have daily, monthly and quarterly Pivot Table reports.
The WEEKDAY function allows you to convert all dates into a number from 1 to 7 representing their weekday, from Monday to Sunday.
You don’t have to use an IF function to get the most out of logic calculations in Excel.
The Australian Financial Year runs from 1 July to 30 June. To determine which quarter a date falls into there are at least a couple of formulas that will do the trick.
In Australia our financial year is from 1 July to 30 June. If you need to work with days elapsed or days remaining in the financial year you can use a few formulas to do the calculations for you.
Ever needed to convert the text Sep or September into a 9? Well Excel can convert text months into their respective numbers.