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)
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
Pingback: A Complete Introduction to Excel | MBAx.me
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!
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!
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
Glad that the videos/lessons have proven to be helpful!
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!
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.
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?
Just a great THANK YOU!