In this lesson, I respond to two recent requests from viewers for help in Using DATE Functions in Excel and also, how to apply Custom Date Formatting. I get similar requests on a frequent basis from both viewers and clients.
Solve Problem #1
My first viewer wants help in using the DATE() Function in Excel. She wants to calculate a date that is 90 days after the starting date for a project. She encountered two problems
- Initially, the DATE() Function produced and error – because she forgot to ‘nest” the YEAR(), MONTH()+9, DAY() Functions inside the DATE() Function.
- She was not expecting a date to be filled in the formulas column when there was no starting date for the project – She needed to include an IF() function to perform a LOGICAL TEST – Does the Starting Date = 0, Value if TURE, ” ” (to display nothing), Value if FALSE – to perform the calculation.
Excel Custom Date Formats
Solve Problem #2
My second viewer wanted my help to ensure that the date values that he “pasted” into a range of cells were in the (Custom) format of “yyyy-mm-dd” (Year, Month, Date).
In this case, we need to take two steps:
- Format the destination cells using the CUSTOM FORMAT of yyyy-mm-dd that we create.
- After “Pasting” the Copied Cells, use the “Paste Options” dialog box to select – “Match Destination Formatting.”
I also demonstrate how to use the NETWORKDAYS() Function and the WORKDAY() Function – with an optional list of “Holidays” to exclude. And, finally, I show you the “pitfalls” that occur when you import a “text” file of dates that use a variety of formats. In order to perform DATE calculations, a date must be stored as a NUMBER in Excel. It must be right-aligned in the cell.
Learn to “Master Excel in Minutes – Not Months!”
How to Use Date Functions and Apply Date Formatting in Excel [ 12:06 ] Play Now | Play in Popup | Download (1075)
Speak Your Mind