Function Problems

Create Year-Based Dates and Work Hours in Excel

Huda QurayshiHuda Qurayshi Nov 20, 2026 869 views

Question details

Create a calendar or work log that generates dates and calculates hours for a year entered in one cell.

Create Year-Based Dates and Work Hours in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the year

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.

2
Create the first date

In A3, enter =DATE($B$1,1,1). Select A3:A368 and apply a date format using Home > Number Format.

3
Fill the date sequence

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.

4
Calculate daily hours

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.

5
Add a yearly total

Place the total outside the date sequence: enter =SUM(D3:D368) in D369. Do not copy the daily formula into that total cell.

Generate the year's dates and calculate daily hours
Adjust existing layouts: If row 369 already contains total hours, keep it as a total row. Adjust the references in the example to match your year cell and the 366 rows reserved for dates.
Work smarter with WPS Spreadsheets

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. 1. Set up the year: Open WPS Spreadsheets, create a workbook, and enter a four-digit year in B1.
  2. 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. 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. 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.
Generate dates for regular and leap years from one year cellCalculate daily and monthly work-hour totals with formulasOpen and save Microsoft Excel XLSX filesUse a familiar spreadsheet interface for free
QA img-9

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.