Dates and date functions

Problems

1. What is the serial format for January 25, 2006?

2. What is the serial format for February 14, 1950?

3. To what actual date does a serial format of 4526 correspond?

4. To what actual date does a serial format of 45000 correspond?

5. Determine the day that occurs 74 workdays after today’s date (including holidays).

6. Determine the day that occurs 74 workdays after today’s date (including holidays but excluding the current year’s Christmas, New Year’s Day, and Independence Day).

7. How many workdays (including holidays) are there between July 10, 2005 and August 15, 2006?

8. How many workdays (including holidays but excluding Christmas, New Year’s Day, and Independence Day) are there between July 10, 2005 and August 15, 2006?

The file Datep.xlsx contains several hundred dates. Use this file for the next set of problems.

9. Determine the month, year, day of the month, and day of the week for each date.

10. Express each date in serial format.

11. A project begins December 4, 2005. The project consists of three activities: Activities 1, 2, and 3. Activity 2 can start the day after Activity 1 finishes. Activity 3 can start the day after Activity 2 finishes. Set up a worksheet that accepts as inputs the duration (in days) of the three activities and then produces both the month and year during which each activity is completed.

12. We bought a stock July 29, 2005 and sold it December 30, 2005. The stock exchange is closed Labor Day, Christmas, and Thanksgiving. Create a list of dates when the stock market was open during the time we owned the stock.

13. The file Machinedates.xlsx contains dates on which several machines were bought and sold. Determine how many months and years each machine was kept.

14. Given any date, find a way to have Excel compute the day of the week of the first day of the date’s month.

15. Given any date, find a way to have Excel compute the last day of the date’s month. Hint: DATE(2005,13,1), surprisingly, returns 1/1/2006.

16. Given any date, how can you compute which day of the year it is? For example, what day of the year will 4/15/2020 be? Hint: The formula DATE(2020,1,0) will return the serial number for day 0 of January 2020, and Excel will treat this as December 31, 2019.

17. In Fredonia, employees have Tuesday and Wednesday off. What date is 200 workdays after 5/3/2013?

18. In Lower Ampere, employees have Friday and Saturday off. Also Valentine’s Day is a holiday. How many workdays are there between 1/10/2014 and 5/31/2015?

19. Suppose the first quarter of each year is January–March, the second quarter is April–June, and so on. Write a formula that returns for a given date the first day of the quarter.

20. Using the definitions of quarters given in Problem 19, write a formula that computes for any given date the last day of the previous quarter.

21. Set up a spreadsheet in which a person can enter his or her date of birth and the spreadsheet returns the person’s actual age.

22. Memorial Day is always the last Monday in May. Set up a spreadsheet in which you can enter the year and the spreadsheet determines the date of Memorial Day.

23. Create a spreadsheet that always lists the next 50 workdays (assuming no holidays and that Monday-Friday are the workdays).

24. Greg Winston works for the federal government, and in 2016 he gets 10 federal holidays off. (Look up the dates of these holidays on the Internet.) Greg also gets every other Friday off, as well as weekends starting with 1/8/2016. Create a list of all the days Greg works in 2016.