Video Details:
- OS: Windows 7 (x64)
- Program: Excel 2007
- Similar Programs: Excel 2010
- Example Files: Date and Time Functions
Goal:
To present the fundamentals of date and time manipulation within Excel 2007.
Tweaks, Shortcuts, & Other Functions Used:
- Microsoft’s Serial Begin Time: 1/1/1900 at 12:00:00 AM
- Static Current Date: CTRL + ;
- Static Current Time: CTRL + SHIFT + ;
- General Format Cells Dialog: CTRL + 1
- Copy Values: CTRL + drag
- Copy Cells with Prompting: RIGHT-CLICK + drag
- Dynamic Current Date: =TODAY()
- Dynamic Current Time: =NOW()
- Standard Preset Date Formatting: CTRL + SHIFT + #
- Standard Preset Time Formatting: CTRL + SHIFT + @
- End of Month: =EOMONTH(start_date, months)
- This value is the numerical serial value for the last day of the month X months from the start date. Since no one outside of Microsoft really prefers to use this as a way of tracking time, it is recommended that you format the result as desired.
- Date/Time Intervals Between Two Values: =DATEDIF(begin_date, end_date,”D”)Workdays Between Dates: =NETWORKDAYS(begin_date, end_date, [holiday_list])
- This function is not well documented within Excel 2007+ and lacks the variable auto-prompts that most other functions possess. You may substitute the below letters in place of D in the above example -
- D for days
- M for months
- Y for years
- This function is not well documented within Excel 2007+ and lacks the variable auto-prompts that most other functions possess. You may substitute the below letters in place of D in the above example -
- Total Workdays: =NETWORKDAYS(start_date, end_date, [holidays])
- This will calculate the sum of the workdays between two specified values, excluding optional holidays as listed; again this will return as the numerical serial value which can be formatted as desired.
- Workday: =WORKDAY(begin_date, workdays)
- Similar to the End of Month function, this calculates the date for the next workday after a specified interval; again this will return as the numerical serial value which can be formatted as desired.
Conclusion:
You should now have a basic grasp of how to implement and manipulate dates and times within Excel. Personally I’d recommend the Lynda.com specific tutorial if you need further video guidance (paid membership required), or if you prefer a hardcopy/e-book version then I’d recommend the Data Analysis and Business Modeling book (see bibliography below).
Bibliography (APA):
- Cox, J., & Lambert, J. (2009). Microsoft Certified Application Specialist Study Guide: 2007 Microsoft Office System Edition. Redmond: Microsoft Press. → Available via Amazon or Barnes & Noble
- Weinman, L. (2012, April 15). Home: Lynda.com. Retrieved from Lynda.com: http://www.lynda.com/ → Specific Tutorial
- Winston, W. L. (2007). Microsoft Office Excel 2007: Data Analysis and Business Modeling (pp. 39-44 & 91-96). Redmond: Microsoft Press → Available via Amazon or Barnes & Noble
- Support, Microsoft. (2011, November 24). How to use Dates and Times in Excel. Retrieved April 18, 2012 from http://bit.ly/JbYTBW
Internal Tracking Only:
- Est. Total Production Time [hrs]: 6






































