Create an Excel Timesheet with a Day Dropdown in WPS Spreadsheets
Question details
The user wants to create an Excel-compatible timesheet in WPS Spreadsheets that uses a day dropdown, start and end time fields, and a formula to calculate hours worked for the selected day.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Building or updating a daily timesheet for tracking work hours with selectable days and automatic calculations.
- Observed behavior
- A working timesheet that calculates hours worked from the selected day and time entries.
Set up columns for Day, Start Time, and End Time in WPS Spreadsheets, and keep the time cells formatted as Time before entering the calculation formula.
Build a Day Dropdown and Calculate Hours Worked in WPS Spreadsheets
Use this method to create a selectable day list and automatically calculate hours from start and end times.
The formula =MOD(EndTime-StartTime,1)*24 handles shifts that cross midnight and returns hours as a decimal number.
Select the cells where you want the day dropdown, click the Data tab, choose Data Validation, and set Allow to List.
In the Source box, type Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday and click OK.
Enter the work start and end times in the adjacent Start Time and End Time cells, for example 9:00 AM and 5:30 PM.
In the Hours Worked cell, enter =MOD(C2-B2,1)*24, replacing B2 and C2 with your actual Start Time and End Time cell references.
Select the Start Time and End Time cells, press Ctrl+1, set the format to Time; select the Hours Worked cell, press Ctrl+1, and set the format to Number.
Use an Excel Table to Automatically Extend the Timesheet
Use this method when you plan to add multiple rows and want the day dropdown and formula copied automatically.
Create a Day Dropdown Timesheet in WPS Spreadsheets
Build a timesheet directly in WPS Spreadsheets using Data Validation for the day dropdown and Excel-compatible time formulas. Save the workbook as .xlsx so it remains compatible with Microsoft Excel without losing the layout or calculations.
- 1. Open WPS Spreadsheets: Launch WPS Office, click Spreadsheets, and open a blank workbook.
- 2. Create the timesheet columns: In row 1, type Day, Start Time, End Time, and Hours Worked as column headings.
- 3. Add the day dropdown: Select the Day cells, go to Data > Data Validation, choose List, and enter the seven days as the source.
- 4. Enter times and the formula: Enter start and end times, then in Hours Worked enter =MOD(C2-B2,1)*24 with the correct cell references.
- 5. Save as an Excel-compatible file: Click Menu > Save As, choose Excel Workbook (*.xlsx), and save the timesheet.
Frequently Asked Questions
How do I create a day dropdown in WPS Spreadsheets?
Select the Day column cells, click Data > Data Validation, choose List, and enter Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday in the Source box.
What formula calculates hours worked from start and end time?
Use =MOD(EndTime-StartTime,1)*24. Replace EndTime and StartTime with the actual cell references, for example =MOD(C2-B2,1)*24. Format the result as Number.
Why does my hours worked formula show a time or a small decimal?
The formula returns hours as a decimal number. Select the Hours Worked cells, press Ctrl+1, and set Number format to Number, not Time. Also verify the Start Time and End Time cells are formatted as Time.
Can this timesheet be opened in Microsoft Excel?
Yes. Save the WPS Spreadsheets workbook as an .xlsx file. The day dropdown and =MOD formula remain compatible with Microsoft Excel.
How do I add the day dropdown and formula to more rows?
Select the last row with the dropdown and formula, then drag the fill handle down. For automatic copying, select the range and click Insert > Table before adding the dropdown and formula.
