25 Date and Time Functions in Excel You Need to Know

Подписаться 65 тыс.

Просмотров 143 тыс.

50% 1

Learn all 25 date and time functions in Microsoft Excel.
1. Dates and Times in Excel 0:24
2. TODAY function 3:24
3. NOW function 4:18
4. YEAR function 4:59
5. MONTH function 5:44
6. DAY function 6:20
7. HOUR function 6:55
8. MINUTE function 8:06
9. SECOND function 8:36
10. EDATE function 9:12
11. EOMONTH function 11:08
12. DATEVALUE function 12:48
13. TIMEVALUE function 14:29
14. WORKDAY function 15:19
15. WORKDAY.INTL function 18:01
16. NETWORKDAYS function 19:43
17. NETWORKDAYS.INTL function 21:43
18. DATE function 23:29
19. TIME function 25:34
20. WEEKDAY function 27:05
21. WEEKNUM function 28:36
22. ISOWEEKNUM function 30:17
23. YEARFRAC function 31:10
24. DAYS function 32:45
25. DAYS360 function 33:26
26. DATEDIF function 34:42
Check out my full courses and ebooks here:
👉 www.howtoexcel...
DOWNLOAD any example workbooks here:
📖 www.howtoexcel...
SUBSCRIBE & get my 3 FREE eBooks.
📧 www.howtoexcel...
CONNECT with me on social:
Facebook: / howtoexcelblog
Twitter: / howtoexcelblog
LinkedIn: / john-macdougall
Thanks for all your support!

Опубликовано:

2 окт 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 54

@HowToExcelBlog 4 года назад

Check out my full courses and ebooks here 👉 www.howtoexcel.org/courses/

@rohitjawale7664 2 года назад

Could that be anymore prettier!!! Simply awesome & professional 👌

@wayneedmondson1065 4 года назад

Hi John.. excellent.. very complete. Worth mentioning is the option of the weekend day "mask" for the two .INTL functions. where you can specify weekend days as a "1" and non weekend days as a "0" in a sequence of 7 numbers between quotation marks. So, "0000000" would be no weekend days; "1000000" would be one weekend day of Monday; "0101000" would be two weekend days of Tuesday and Thursday.. and so on. This feature can be useful in certain circumstances or and situations. Thanks for the videos and good learning at your channel. Thumbs up!!

@HowToExcelBlog 4 года назад

I definitely would have mentioned it if I had known it but it's new to me. That's a cool tip. Thanks! 😀

@jimmylui4888 4 года назад

Timestamps for copying into Description 1. Dates and Times in Excel 0:24 2. TODAY function 3:24 3. NOW function 4:18 4. YEAR function 4:59 5. MONTH function 5:44 6. DAY function 6:20 7. HOUR function 6:55 8. MINUTE function 8:06 9. SECOND function 8:36 10. EDATE function 9:12 11. EOMONTH function 11:08 12. DATEVALUE function 12:48 13. TIMEVALUE function 14:29 14. WORKDAY function 15:19 15. WORKDAY.INTL function 18:01 16. NETWORKDAYS function 19:43 17. NETWORKDAYS.INTL function 21:43 18. DATE function 23:29 19. TIME function 25:34 20. WEEKDAY function 27:05 21. WEEKNUM function 28:36 22. ISOWEEKNUM function 30:17 23. YEARFRAC function 31:10 24. DAYS function 32:45 25. DAYS360 function 33:26 26. DATEDIF function 34:42

@HowToExcelBlog 4 года назад

Thanks again, I finally got around to adding these in!

@omkarnathkaja300 2 месяца назад

Excellent

@johnpap675 3 года назад

This had to be done by proffesional,appreciated.

@cambike 4 года назад

Would be super useful to do a tutorial on Time to decimal Time. EG 12:30 (12 hours 30 minutes = 12.5 hours), this is such a difficult function, even harder when more than 24 hours, please do a lesson on this, loads of people need it. Excellent video as normal

@tarunverma4786 2 года назад

really helpful

@ariskavanessen7430 4 года назад

What happens with Time Value when you have a text written time that could be an or pm? E.g. 11:30. As no AM or PM is given does excel automatically assume it is the 24 hour clock. Thank you.

@kaychapman3161 2 года назад

I found this video because I'm trying to figure out how to compare calculated elapsed time which is a value of "28:26:20" against a set threshold of 24 hours. when using comparison operators it seems to not recognize the hard value "24" as hours. This can't be that difficult but I can't find an answer anywhere

@paulvanobberghen 4 года назад

FINALLY! Someone who explains dates and time in Excel properly! Congrats!

@HowToExcelBlog 4 года назад

Thanks Paul!

@ChrisMcDonald-z8d 10 месяцев назад

How do I customize the WORKDAY function if my work week contains Saturdays and Sundays on Mac?

@akram2kbd 3 года назад

How can i get one cell data update information to other cell where I can find last updated date and time automatically?

@paulvanobberghen 4 года назад

Though it’s good practice to manually close a function with a right parentheses, you can actually type tab or return if Excel expects no other mandatory arguments. Type now( + enter, for instance. Optional arguments will use default value. Not an option in complex formula with nest functions, though.

@HowToExcelBlog 4 года назад

Yes, I press enter usually omit the end parentheses.

@blessedowo1958 2 года назад

It is a good practice to close parenthesis as it builds up a habit which will be crucial when making nested functions. Also, not closing the parenthesis will not work for mobile version of sheets.

@rrynamariano7826 Год назад

Hi. How can I compute this. If the start date si June 3 2021 and end date is March 11 2023 How many months and days? But I need it was converted as months The answer should not 21.8 because it is literally 21months and 8 days Pls help to convert it in months including the days Thank you

@excelent8567 4 года назад

Easy trick to always get the correct last day of the month is using a combination of the EOMONTH and DATE formulas. The day should not be filled in the DATE formula. It works as follows for 29/02/2020: =EOMONTH(DATE(2020,2,),1). The trick is that not filling out the day number in the date formula, Excel interprets it as the last day of the previous month (= day zero). By then combining it with EOMONTH, you have an easy way of always getting the correct end date of the month, whether the month has 28, 29, 30 or 31 days.

@HowToExcelBlog 4 года назад

Great tip! Thanks!

@chahineatallah2636 4 года назад

Hi guys , what is the difference between weeknum and isoweeknum function? Couldn’t get what is the diff? Can someone explain more? Thanks

@HowToExcelBlog 4 года назад

ISO is based on a weird weekly calendar system that can have 53 weeks instead of the normal 52, and you can likely safely ignore this function for your entire Excel career without worry. 😀

@idwtv534 Год назад

Does anyone know how to create a timezone sheet in excel where i enter a time of a certain time zone and excel shows me the time in different time zones. Any tutorial would help

@sofiajorge-mercury7504 2 года назад

Hello I need to calculate time elapsed between date and time of a task being received and completed. I need to exclude weekends and it must only calculate time within 08h00 and 17h00 = can you please assist?

@steventate9276 2 года назад

Hay the guy on the radio said that Y@K is coming back. I d better stock up.

@darrylmorgan 4 года назад

Great Tutorial! Really Well Explained..Thank You John :)

@Samu-gz3qj 5 месяцев назад

Wow

@bernard_apostol28 Год назад

how to find in a list the dates of today and after, and display them in a cell?

@paulvanobberghen 4 года назад

You can combine date and time as a time stamp by typing ctrl semicolon then a space and ctrl Colon. Equivalent of now() but as a number, not a formula.

@HowToExcelBlog 4 года назад

Good tip!

@paulvanobberghen 4 года назад

@@HowToExcelBlog For sake of completeness, on the macOS version of Excel AND an Azerty Keyboard, current time is given by both command ⌘ + semicolon ; and command ⌘ + colon : and current date is given by both control ⌃ + semicolon ; and control ⌃ + colon : Maybe because colon and semicolon are on 2 different keys, while they are on the same key on a Querty keyboard? Under Excel for Windows and an Azerty keyboard, it's the same as described in the video with ctrl key only, just on 2 different keys. Can't verify on a macOS version of Excel and a Querty keyboard, though.

@E1knight 4 года назад

a last I get something better than usual.... thank you man..

@HowToExcelBlog 4 года назад

😊

@bfmcpoufa133 4 года назад

Used yearfrac right away. Thanks

@HowToExcelBlog 4 года назад

Good stuff. I still haven't come across a use for ISOWEEKNUM or DAYS360 yet. 😂

@lee-jg6eq 4 года назад

Clear, concise and professional tutorial - thank you:-)

@HowToExcelBlog 4 года назад

You're welcome 🙂

@ubaidillahmuhammad20 4 года назад

nice. share the file

@HowToExcelBlog 4 года назад

Link is in the description 👍

@stewardgiroro7929 2 года назад

Thanks John for the tutorial.

@lccate2441 3 года назад

Very nice thank you for the info

@touchthewinderestu3597 3 года назад

i found a short cut in the first 2 minutes !

@HowToExcelBlog 3 года назад

Bonus!

@Levendo 3 года назад

Thank you, this is exactly what I needed.

@mcmielnik 4 года назад

First! :)

@HowToExcelBlog 4 года назад

😂

@rscsurveillance179 2 года назад

Can you add a Now() function without it updating the time after every entry

@vahidshojaie263 Год назад

i have that problem too. could you find any answer for it?

@franciscoruiz7038 3 года назад

I try your formulas but didnt work

@Franklin-pc3xd Год назад

Huh? The earliest possible date is January 1, 1900?? There are a lot of folks from the 19th Century and earlier that are rolling over in their graves right now.