Create Year-Based Dates and Work Hours in Excel
Question details
Create a calendar or work log that generates dates and calculates hours for a year entered in one cell.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to reuse one worksheet for different years and summarize work hours by month.
- Observed behavior
- Changing the designated year should update the date sequence, including leap years, without requiring a separate table for each year.
Choose a cell for the year and reserve 366 rows for dates so the sheet can accommodate leap years. Place totals and monthly summaries outside those date rows.
Generate the year's dates and calculate daily hours
Use this layout to update a work log when the year changes and stop the date sequence at December 31.
Enter a four-digit year, such as 2026, in cell B1. Reserve A3:A368 for dates, B3:B368 for worker counts, and C3:C368 for hours per worker.
In A3, enter =DATE($B$1,1,1). Select A3:A368 and apply a date format using Home > Number Format.
In A4, enter =IF(A3="","",IF(YEAR(A3+1)=$B$1,A3+1,"")), then drag the fill handle down through A368. The final row remains blank in a non-leap year.
Enter worker counts in column B and hours per worker in column C. In D3, enter =IF(A3="","",B3*C3), then fill the formula down through D368.
Place the total outside the date sequence: enter =SUM(D3:D368) in D369. Do not copy the daily formula into that total cell.

Summarize work hours by month
Use a monthly summary when you need totals for each month from the daily work log.
Build a reusable yearly work log in WPS Spreadsheets
Use WPS Spreadsheets to generate dates from a year cell, calculate daily work hours, and summarize them by month in one reusable workbook.
- 1. Set up the year: Open WPS Spreadsheets, create a workbook, and enter a four-digit year in B1.
- 2. Generate dates: Enter =DATE($B$1,1,1) in A3. In A4, enter =IF(A3="","",IF(YEAR(A3+1)=$B$1,A3+1,"")), then fill it down through A368 and format the cells as dates.
- 3. Calculate daily hours: Enter worker counts in B3:B368 and hours per worker in C3:C368. Enter =IF(A3="","",B3*C3) in D3 and fill it down through D368.
- 4. Summarize and save: Enter =SUM(D3:D368) in D369 for the yearly total. For monthly totals, list month numbers in F3:F14 and use the SUMIFS formula from the monthly summary solution; then use File > Save As to save the workbook as an XLSX file.

Frequently Asked Questions
How does the date sequence handle leap years?
The formula checks whether the next date is still in the year in B1. With 366 date rows, it includes February 29 in a leap year and leaves the last row blank in a non-leap year.
Should I delete A369 if row 369 contains my total?
No. Keep row 369 outside the date sequence and use D369 for a total such as =SUM(D3:D368). Reserve A3:A368 for up to 366 dates.
Why does Excel highlight [@workers] in my formula?
The reference [@workers] is an Excel table structured reference to the workers column in the current row. Check the table's column name, or use a regular cell reference such as B3 if your data is not in a table.
How can I total hours for one month?
Put the month number in F3 and use the SUMIFS formula in the monthly summary solution. It sums D3:D368 where the corresponding date in A3:A368 falls within that month of the year in B1.




