# Excel Video Lesson: Date & Time Functions

As listed in the Master Workbook, there are functions grouped into categories. This video is a demonstration of the Experience Level 1 Date & Time functions.

The functions covered in this video lesson are:

DATE — DATEVALUE — TODAY  — DAY  — MONTH — YEAR
TIME — TIMEVALUE — NOW — HOUR — MINUTE — SECOND

CORRECTION: I mention in the video that Excel views dates as the number of days since January 1, 1900 but it is actually January 0, 1900 (a bit confusing since January 0 doesn’t make intuitive sense).

Now that you’re an Excel ninja when it comes to Date & Time functions, try mastering Text Functions (e.g. LEN, CONCATENATE)

This entry was posted in Function-Related Lessons and tagged , , , , , , , , . Bookmark the permalink.

### 27 Responses to Excel Video Lesson: Date & Time Functions

1. Kalpith Navlakha says:

Hi,
I need to the function/ command, where the date changes automatically if the date entered falls on Saturday/Sunday.

For Eg: If i write 13th May 2017(Saturday), it should either automatically change to 12th May or it should give me a warning that it is a Saturday/Sunday.

Could you help me with this?

Regards,
Kalpith

2. raavi says:

i need to make a table with date and days like this for the entire year.

Sunday, April 30, 2017
Sunday, April 30, 2017
Sunday, April 30, 2017
Sunday, April 30, 2017

so a day and its date should come for four rows and next day and date in next four rows , like this. Is there any shortcut methods.

3. Sara says:

Hello Ben! Thanks so much for your helpful videos! I have a little issue with language/regional settings. I’m in Spain, and even though my Excel is in English, the formula to get the year (tab 4 in the Master Workbook, cell C15) doesn’t work, only if instead of YYYY I type the Spanish AAAA. Do you have a solution for this?

4. Ben says:

Hey Ben,
When I type in the datevalue (=DATEVALUE(C10&”/”&C11&”/”&C12) it just says #VALUE!.

5. Anonymous says:

Great explanations, but the voice makes every thing boring.

• Anonymous says:

shut up

6. Anonymous says:

this video was made just 7 days after the horrific 9/11 !!!…..

• Anonymous says:

That was 2001 not 2011

7. Pam says:

Ben,
Thank you for these videos. They are very helpful as I brush up and learn more than I ever thought I would need to know about excel. I am trying to track down the best way to perform a calculation on the month from a date field. The Month(cell) extracts the number of the month and want to sue that is a calculation something like Month(cell)+6. Can you point me in the direction where I can find that? It doesn’t work the way I thought it would.

8. Charla says:

I see a lot of interesting articles on your page.

You have to spend a lot of time writing, i know
how to save you a lot of time, there is a tool that creates unique, SEO friendly posts in couple of seconds, just search in google – k2 unlimited content

9. Anonymous says:

i type date give result to coded and in serial 1 to 999
example: type 01/01/2013 and give result 01012013001 to 01012013999

10. Anonymous says:

Hi, just a quick question:

Is there a difference between saying that Excel views dates as the number of days since December 31, 1899 as opposed to January 0, 1900?

Thanks for the excellent tutorials by the way. These have been a great help for me.

• Hey there,

Great question 🙂 There isn’t anything wrong with thinking about the two the same way, but literally Excel views a 0 date as 1/0/1900 rather than 12/31/1899. So for calculation purposes, it’s fine to say, but not when discussing how Excel views dates.

Here’s a screenshot showing how a zero shows up formatted as Date:
http://wp.me/a1T6xx-my

Love that you’re thinking this deeply about things, and I agree, very strange to have a 0 day date.

Cheers,

Ben

11. SuzieQ says:

This lesson is not working.

• Anonymous says:

it takes longer to type in the formula to have the date showing up as text rather than typing the date directly as text ?!

• Anonymous says:

okay got it, meant to use on large data….

12. WC says:

Is there a way to tell the cell to give me the previous month based on the month of today or now function? For example if the Today gives me September, is there a way for it to give me August (the month before?).

• Hey WC,

There certainly is! You would use the EOMONTH formula (which is for end of month).

For example, if you used

=EOMONTH(NOW(),0)

it would come up with March 31st, 2013. If you did =EOMONTH(NOW(),-1) if would be February 31st, 2013, and so on.

You don’t have to use the now formula, you can use it based on any date or cell with a date.

Hope that helps,

Ben

13. stephanie says:

I love this Ben, I was looking for a way to learn more about Excel and you have done an incredible job, thanks! I have a question about dates…you mention having a large group of dates in a column, which is what I have. I study training patterns of racehorses and am interested when they do not train. So my column of dates is a record of their daily activity on the racetrack. When they do not train the date is missing. So for example I may have in a column 30 dates for a month, and then 15 dates for the second month and then the 16-23 days of that month there is nothing listed and the column would resume with the 24th date of that month. Is there anyway Excel can identify when that gap or break in the normal “counting” of dates? When I have finished the entire lesson I will contact you about consulting but couldn’t help beginning to think of ways Excel might help me NOT scroll through data row by row!!!!! Anyway, GREAT SITE!

14. Anonymous says:

Thank you, this is great! 🙂
I just did the first lesson today, but I’m going to do the rest as well.

Love these videos man, they’re extremely helpful. Keep up the good work!

16. aedittrich says:

Hello,
I’m so thankful for your lessons in excel and so is my management! I had a quick question though. When I go to convert my Timevalue it doesn’t convert to a percentage like yours. Mine read “0.524074074”. What am I doing wrong?
Thanks so much.
-A

If you look at 11:28 in the video, you’ll see that I already have that cell formatted as a ‘Percentage’ before I enter the formula (in the ribbon menu near the top-middle of the screen). Yours would come out to be about 52.4% if you changed it to show as a percentage. (Remember, in math 52.4% is the same as 0.524 since it just means 52.4/100).

Mine would have likely shown up the same as yours if I hadn’t already formatted the cell before the video.

Hope that helps!

17. Thanks for the feedback. Glad you enjoyed the video and could follow along in the spreadsheet.

I will be varying which types of lessons are posted (but all will be categorized on the right for easy navigation).

In reference to your question, that is called ‘grouping’. A shortcut way to group rows or columns is to highlight the rows/columns you wish to group and use ALT+SHIFT+RIGHT ARROW to group the rows/columns, and ALT+SHIFT+LEFT ARROW to ungroup them. You can go multiple levels as well (so you could group rows 1-30, and then group rows 20-25 as a subgroup of the first). The levels are displayed at the top of the red box in your image and can be clicked to view the entire sheet at any of the levels.

Here’s a screenshot explaining how you can do this using the menus in Excel:
http://i.imgur.com/ViNTp.png

Hope that helps, I should be posting some more videos tonight or tomorrow.

18. haeso says:

I like the pace and length of the video. I’m looking forward to the next lesson.

The format is good when shown in full screen mode. I looked at the video and kept notes – and then opened the excel-file to practice.

Other than functions, I’d like to know how the “expansion column” ( http://i.imgur.com/tz1kz.png ) to the left is created, maybe that will be revealed in a future lesson?

• Anonymous says:

Just a great THANK YOU!