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

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

@HowToExcelBlog

4 жыл бұрын

Thanks Paul!

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

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

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

Thank you, this is exactly what I needed.

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

@HowToExcelBlog

3 жыл бұрын

You're welcome 🙂

Thanks John for the tutorial.

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!

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! 😀

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

@HowToExcelBlog

4 жыл бұрын

😊

Very nice thank you for the info

Excellent

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.

Used yearfrac right away. Thanks

@HowToExcelBlog

4 жыл бұрын

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

This had to be done by proffesional,appreciated.

really helpful

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.

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

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

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

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.

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. 😀

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

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?

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!

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

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?

Wow

nice. share the file

@HowToExcelBlog

4 жыл бұрын

Link is in the description 👍

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

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

First! :)

@HowToExcelBlog

4 жыл бұрын

😂

i found a short cut in the first 2 minutes !

@HowToExcelBlog

3 жыл бұрын

Bonus!

I try your formulas but didnt work

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.