Beginner Survival Guide To Working With Dates And Times In Excel

For me, working with dates and times is the most frustrating thing about Excel. No exaggeration – it’s genuinely the most frustrating thing.

00:00 INTRODUCTION

I’ve wasted hours and days trying to resolve problems with times and dates in Excel. And, I have to admit, I’ve still not mastered it. But, I have learned a lot. In this video, I share what I know about times and dates in Excel, from the concept of how Excel understands dates and times, to helpful date and time formulae, and typical situations you might encounter - and how to deal with them. So, let’s get started with times and dates in Excel …

💻EXCEL DOWNLOAD FILE

🔥EXCEL FORMULA TRAINING TOOL

00:35 CONCEPT: HOW EXCEL TREATS DATES

The first thing to understand is that Excel treats dates as numbers. Yes, they appear in date ‘format’ (eg. MM/DD/YY), but the underlying data is a number – it’s just the formatting that makes it appear different.
To test this, type ‘1’ into a cell, then convert the cell formatting to ‘Date’, as Chris does in the video. The result should be ‘01/01/1900’. What’s the significance of this date?

Well, it’s what Excel understands as the ‘beginning of time’. This, for Excel, is date no.1 and it’s represented by ‘1’.

So, if Excel actually understands dates as numbers, what’s today’s ‘day number’? To understand this, let’s reverse the process and discover a few new formulae as we go. Input the =TODAY() formula into a cell as Chris does in the video. Today’s date should automatically appear – note how Excel autoformats the cell to display a date. But, we know the underlying value is simply a number – so what is it? To find out, clear the formats from the cell. This reveals today’s ‘day number’ and reinforces the idea that Excel actually understands dates as numbers. Got it? Good!

02:45 CONCEPT: HOW EXCEL TREATS TIMES

In addition to the TODAY formula that tells us today’s date, the =NOW() formula is also useful and teaches us how Excel understands times. Type =NOW() in the cell below and let’s compare the two. Once the formats are removed from the cell, you can clearly see a decimal. What’s that all about? Well, we’re familiar with the day number from the previous formula, so what does the decimal portion of this number represent?

🔥EXCEL FORMULA TRAINING TOOL

To find out, make Excel calculate as Chris does in the video, and note how the decimal ticks over. Yes, this decimal actually represents time! That’s because Excel understands time as a fraction of a whole number. It kind of makes sense since we could think of time as a portion of the day that has gone – at least this is how Excel thinks about it! Reinforce this idea by typing some decimals into the spreadsheet and formatting them as time.

04:18 14 USEFUL FORMULAE FOR DATES / TIMES IN EXCEL

As we have seen, how Excel (auto-) formats your data is important. Rather than playing with cell formatting, however, I recommend using Excel’s date formulae to extract the ‘component’ of the time / date that you need. Want a cell to display the month of a date, for example? Use the =MONTH() formula. There are others too, right down to the second! And don’t forget the =WEEKDAY() formula that translates the day of the week into a number, starting from 1 for Sunday.

Don’t forget the basic idea – that Excel understands dates as whole numbers and times as decimals: this should begin to make things easier. This means we can use basic arithmetic formulae, for example, to find the difference between two dates, as Chris demonstrates in the video.

07:06 FIND NO. DAYS BETWEEN TWO DATES

Oh, you want to know more about the difference between two dates in Excel? Yes, the basic subtraction method works, but there are some other cool formulae you should know. =DAYS() does the same thing in a tidier manner. The =EOMONTH() and =EDATE() formulae are even better: the first gives the date of the last day of the month, while the second returns the ‘equivalent’ date in a different month. Yes, because not all months have 31 days, these formulae are essential for navigating the calendar. =NETWORKDAYS(), meanwhile, finds the difference between two dates counting workdays only. Cool!

10:40 3 COMMON PROBLEMS AND HOW TO DEAL WITH THEM

Now you know the basics, what common problems might you encounter when working with dates and times in Excel, and how would you deal with them? In the video, I share 3 fixes I use all the time in my Excel development work.

So, there you have it, my survival guide for working with times and dates in Excel. Any nightmare stories to share about working with times / dates in Excel? I’ll lend an ear in the YouTube comments and I hope these tips help you get to grips with this tricky topic.

🔥EXCEL FORMULA TRAINING TOOL

[TigerSpreadsheetSolutions]

🔥FREE DOWNLOAD: EXCEL FORMULA TRAINING TOOL

[paultaylor]

Easiest way to sum hours over 24 or even minutes/seconds over 60 is just to change format so the item you need to add is in [ ] eg [h]:mm:ss to show hrs, if you need in minutes and seconds [mm]:ss or [mm] if you don't need seconds or to just show in seconds [ss]

[CatLowe]

Really helpful video thanks! I'm a complete beginner but using the MOD function to calculate duration of a work shift is one of my favourite discoveries so far. I don't actually understand how and why it works but it does 😄

[_maik_]

Great Tutorial 👍

I always get in trouble, when I get my Data from a different system.

The date is displayed as e.g. "121020" which should be in a correct format like this 12.10.20 (German)

Do you have a good idea how to convert this?

[markstainer]

How does UKs day light saving get adjusted into these formulas?

[matthewhastings]

Great vid 🎉 not sure why it has taken a year to get onto my YouTube

[marcusbeech]

Hi there when the next members Monday? Now I am a member will I get a notification? Cheers all

[Husky_Passion]

the most frustrating is without a doubt when suddenly, fo no reason at all, it crashes, or a save is malfunctionning, stuff like that