Let’s say you are transitioning to retirement (lucky you) and you only work four days a week. You have Wednesdays off to play golf. You may still do projects and you need to figure out completion dates based on a start date and working days. Excel can help you.
I wrote an article years ago explaining how to use a related table to handle financial years in Excel Pivot Tables. You can read the article here. If you only want the months in financial year order you can just add an extra column to your table.
I had a recent query regarding checking time in a column that had both date and time. There is an easy way to extract time from a date-time combination.
On the weekend Sunday the 5th of September 2021 is a big day for Excel.
In Excel the date system starts on 1/1/1900 – that was day 1, and each day since has its own sequential number.
On Saturday September 5, 2021 (Father’s day in Australia) that number is – wait for it – see the image below.
May the fours be with you!
If you remove the formatting from a cell with a date you will see the underlying number.
In this short video I cover how to increment dates in long ranges.
It uses a little know dialog.
In this short video I cover how to insert dates in long ranges.
Its simple and quick.
You can use a keyboard shortcut to enter today’s date in a cell, but you can also use it in lots of other places in Excel.
When working with weeks in Excel you may need to show the start and end date of the week in the same cell. Here’s how you can do that.
In this post I finish off the Calendar matrix by adding holidays.
The SEQUENCE function returns sequential numbers. Let’s see how we can use it to create a Calendar matrix.
The SEQUENCE function returns sequential numbers. Let’s see how we can use it with a list of dates.
A few years ago I wrote an article on extracting the end of quarter date from a date. I recently had a query that was related and I tweaked the previous solution to solve it.
On a recent Webinar I was asked a question about an unusual date structure that was imported. The structure dd.mm.yy was not recognised by Excel as a date. Here is formula that fixes it.
Below is an example of the date issue.
The formula in cell B2 is
As you can see the dates in column A are left aligned. That is a clue that they are not recognised as dates in Excel. Dates are right aligned.
The SUBSTITUTE function replaces the full stop between the numerals with a / and makes it look like a date.
This isn’t sufficient as the SUBSTITUTE function will return text. The *1 at the end converts the text date in to a real date that Excel recognises.
Note: Power Query can also automatically fix dates like these when it imports data.
As per a comment from Rick Rothstein Excel MVP you can use the Text To Column feature to fix the dates in place.
The Indian Financial Year start on 1 April. Like Australia its Financial Year month numbers can be painful. Here is a formula to sort them out.
In Excel we tend to work in years, months or days. There may be times when you want to work in weeks.
Let’s say you have an input range that covers the whole year. You only want users to make entries in the current month column. How can you limit the month entry? The answer is a custom Data Validation.
Unfortunately Excel doesn’t have an ISDATE function. Excel’s macro language, VBA does, but there is no spreadsheet function that let’s you know if a cell contains a date. Well there is a partial workaround and you can also use VBA.
There are only two characters Excel recognises when separating numeric days, months and years in dates. They are the / and – characters.
Please don’t use the full stop as Excel won’t recognise it as a date.
Below you can see examples of using / and – in dates.
When you use the full stop Excel won’t recognise it as a date – see below.
Let’s say you want to allocate a value across multiple months based on a start date and how many months you want to allocate. The monthly allocation will be averaged based on the number of months. The solution isn’t that hard.
Let’s say you have a weekly roster. You have a date and you need to identify the Monday start date for the week that contains that date. The formula is fairly easy.
If you have the latest Excel version or the subscription version, you may have noticed some refreshing improvements to PivotTables.
Timeline charts are an effective way to display events over time. You can use a new Excel 2016 feature to easily create a timeline chart.
When plotting Actuals and Forecasts on a single line chart you may want to use a vertical dotted line to identify where the Actuals finish and the Forecasts begin.
Here’s another way to create a Step Chart. This one is quicker. I wrote previously about using a scatter plot and error bars but it required a lot of chart changes. This one hacks a line chart and requires no chart changes.
Grouping is a powerful feature in PivotTable reports but sometimes Excel won’t let you apply grouping. There are a few reasons for this.
Recently a client wanted help in summarising a large data list of employees. They wanted to identify the years of service in terms of 5, 10, 15 years and other milestone years based on a start date.
Getting dates into order is usually a job for Power Query, but not everyone has it or uses it so I still get requests for formulas to fix text dates.
There are a number of mouse and keyboard shortcuts for copying. But there is one type of copy that can be frustrating. Copying dates can be challenging because, in general, Excel wants to increment them, not copy them. There is a simple technique to instruct Excel to copy a date.
You can create complex functions with Excel to handle dates. But it makes more sense to get your data structured correctly and then you can use simpler date formulas. Power Query allows you to fix your data so that you can use those simpler formulas.
What is the best layout when working with months/quarters/half years and full years? There are a few common structures. I prefer the one that lets you create single formulas that can be quickly copied across and down with as few copies as possible.