The # symbol is used in Excel sheets for hyperlinks and dynamic arrays. It is also used in VBA for date definitions.
Tag Archives: dates
Yesterday and Tomorrow in Excel
I recently saw a post about using a LAMBDA function for the dates for yesterday and tomorrow. You don’t need to use LAMBDA, you can just use range names.
Formats Affect Excel’s Fill Handle
Applying a date format before you drag with the Fill Handle may save you some time. See why.
Excel Month List Custom Function
It is common in reporting files, budgeting and forecasting files to create a list of months going across the page. The custom function in this post can automate the process.
Convert Day Text into Day Number in Excel
Excel uses a number from 1 to 7 to refer to the days of the week. The WEEKDAY function returns the number based on a date. Excel doesn’t a have a function to return the day number based on a text day like Monday. Time for a custom function.
Highlighting Weekends in Excel
When you have a list of dates in Excel it can be useful to know which of those dates are weekends. You can automate a format for weekends using a conditional format.
Custom Function for an Ordinal Date
A while ago I posted about creating an ordinal (1st, 2nd etc) for a date. With the inclusion of the LAMBDA function I thought I would create a custom function to simplify the process.
Percentage of the Year in Excel
As we get used to the new year we may want to perform some calculations based on the old year. A recent inquiry requested a formula that could calculate the percentage of a year that an employee had been employed. He suggested using an IF function. See the solution below, but it doesn’t involve the IF function.
Custom Function for Age in Excel
Calculating age with years months and days takes several functions to create. Whenever you need multiple functions to calculate an entry you may have a good use case for a custom function.
Date Alignment Trick in Excel
Text alignment in Excel is versatile. If the column isn’t wide enough to display the text, it will display over the next cell. Date and number alignments are not so forgiving. If the column isn’t wide enough the cell with display the ### symbols or the scientific format. Here is a function technique to get around the limitation.
Excel Month on Month Movement
A PivotTable solution
If you need to find the movement from the previous month a PivotTable can be your friend and do most of the work for you.
MIN and MAX and Dates in Excel
Automating the latest date
The MIN and MAX functions can provide easy ways to capture current dates.
Working with a Different Working Week in Excel
International functions to the rescue
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.
Financial Year Month in a Pivot Table
Create new column
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.
Extracting Time from Date and Time in Excel
Another MOD function solution
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.
May the fours be with you
Excel and Star Wars combine
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.
One Minute to Excel #8 – Incrementing a Long List of Dates
It is easy when you know how
In this short video I cover how to increment dates in long ranges.
It uses a little know dialog.
One Minute to Excel #7 – Copy Date down a Long Range
Quick and easy technique
In this short video I cover how to insert dates in long ranges.
Its simple and quick.
Entering today’s date in Excel
It works in lots of places
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.
Text for a week in Excel
Time to TEXT
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.
Using SEQUENCE with Dates in Excel Part 3
How to highlight specific dates
In this post I finish off the Calendar matrix by adding holidays.
Using SEQUENCE with Dates in Excel Part 2
Time to enter the matrix
The SEQUENCE function returns sequential numbers. Let’s see how we can use it to create a Calendar matrix.
Using SEQUENCE with Dates in Excel Part 1
Date listing
The SEQUENCE function returns sequential numbers. Let’s see how we can use it with a list of dates.
Extracting End of Quarter Dates in Excel
Another MOD solution
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.
Fix dd.mm.yy date format
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
=SUBSTITUTE(A2,".","/")*1
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.
Added 17/11/2021
As per a comment from Rick Rothstein Excel MVP you can use the Text To Column feature to fix the dates in place.
Related Posts
Indian Financial Year Month Number in Excel
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.
Weeks and days calculation in Excel
In Excel we tend to work in years, months or days. There may be times when you want to work in weeks.
Restricting column inputs based on the current month
A data validation solution
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.
Excel ISDATE Function
A few solutions
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.
Entering Dates in Excel
Stop the full stop
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.
Its left aligned and will be treated a text.