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.
Tag Archives: dates
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.
Allocate based on start date and number of months
EDATE to the rescue
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.
Excel Formula Week Starting with a Monday
The WEEKDAY function has options
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.
Updates to Pivot Tables
Time-saving improvements
If you have the latest Excel version or the subscription version, you may have noticed some refreshing improvements to PivotTables.
Creating a Timeline Chart in Excel 2016
Error Bars plus a new feature
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.
Adding a Vertical Dotted Line to an Excel Line Chart
An error bar solution
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.
An Easier Step Chart
A Line chart solution
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.
PivotTable Grouping Doesn’t Work
Rogue data the reason
Grouping is a powerful feature in PivotTable reports but sometimes Excel won’t let you apply grouping. There are a few reasons for this.
Identify Milestone Years in Excel
MOD function to the rescue
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.
Another date solution
Formula to the rescue
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.
Copying a Date Down a Long List in Excel
Two techniques make it quick and easy
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.
Dates are Easy with Power Query
Getting the date right and save time
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.
Easier Structures in Excel
Some are easier to use than others
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.
Count Sundays between two dates
SUMPRODUCT shows it flexibility
We’ve all heard the term “A month of Sundays” to describe a long time. Well what if you wanted to count how many Sundays between two dates?
Getting Date Headings Right
Formulas rule
If you are using date-based headings in your reporting models please consider using dates in the headings rather than text. I’ll explain why.
The NETWORKDAYS.INTL Function Has a Unique Trick
Its customisable
The NETWORKDAYS.INTL function was added in Excel 2010. It allows to calculate how may work days between two dates using non-standard weekends. Some countries don’t have Saturday/Sunday weekends.
How Many Fortnights in a Month
Great for cashflow calculations
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.
Converting a Tricky Date Format
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.
Custom List Trick
Create looping sequential codes
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!
Another Excel Date Issue Solved
Times Affecting Date Calculations
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.
VBA to Open CSV and Avoid Date Error
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.
Getting the Week Number From a Date in Excel
New WEEKNUM Function - ISO 8601 standard
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.
Adding years or months to a date in Excel
Finding the end date can be easy
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.
Ordinals For Dates
1st,2nd,3rd,4th
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.
Working with Imported Dates and Times in Excel
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.
Pivot Tables and Different Date Grouping
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.
How to use the WEEKDAY function
The WEEKDAY function allows you to convert all dates into a number from 1 to 7 representing their weekday, from Monday to Sunday.
Logic Calculations in Excel
Make the most of TRUE and FALSE
You don’t have to use an IF function to get the most out of logic calculations in Excel.