The MIN and MAX functions are useful as for returning the first and last dates in a range. They have conditional versions as well MINIFS and MAXIFS which allow you to find the earliest and latest dates based on a condition.
(more…)
Excel is built to work with calendar years. Financial years pose a few issues for correctly identifying year and month numbering. I wanted to create some formulas that would work with all financial year ends. Here they are.
(more…)
Sometimes you need to know accurate completed years based on a start date and today’s date. Here is a simple formula to calculate that.
(more…)
Power Query can import table-based data direct from the web. As an example, we can extract the public holiday dates for Western Australia from the WA government website. This process is sometimes called web scraping.
(more…)
Let’s say you need to classify a series of dates as either Weekday, Weekend or Holiday. Here’s a formula and a custom function you can try.
(more…)
If you need to enter a date in a formula you typically use the DATE function to create the date. You can enter the date as Text and Excel will convert it for you.
(more…)
Excel can easily add days to a date but months, quarters and years require a slightly different calculation. Here’s a single function that does all three.
(more…)
Excel has an EOMONTH function that returns the end of month. You can hack this function to return the start of the month as well. I thought I would make it easy to return the first, last or middle of the month in one simple function.
(more…)
I frequently use the shortcut Ctr + ; to enter today’s date in a cell. I thought it would be useful to be able to enter yesterday’s date in a cell. I wrote a one-line macro to do it for me.
(more…)
Often when you import data into Excel the dates may include a time. This can make summarising the data more difficult as time is included as a fraction of a date. You can use an old function to fix the problem. This solution requires the subscription version of Excel.
(more…)
One of my more popular posts involved counting the number of Sundays between two dates. With dynamic arrays that becomes easier, and we can create a custom function.
(more…)
Creating a Dynamic list of dates in Excel is pretty easy now with the use of the SEQUENCE function. Creating a dynamic list of weekday dates is a little bit more complex.
(more…)
The # symbol is used in Excel sheets for hyperlinks and dynamic arrays. It is also used in VBA for date definitions.
(more…)
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.
(more…)
Applying a date format before you drag with the Fill Handle may save you some time. See why.
(more…)
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.
(more…)
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.
(more…)
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.
(more…)
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.
(more…)
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.
(more…)
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.
(more…)
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.
(more…)
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.
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.
(more…)
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.
(more…)
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.
(more…)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…
In this short video I cover how to increment dates in long ranges. It uses a little know dialog. https://vimeo.com/507886312
In this short video I cover how to insert dates in long ranges. Its simple and quick. https://vimeo.com/507884321