Excel Formula to Return Shift Start Times From Shift Codes
Question details
Assign a start time in column C according to the shift code entered in column J.
- Product
- Microsoft Excel spreadsheet formula
- Device & OS
- not provided
- Scenario
- The user needs to convert S1, S2, and S3 shift codes into corresponding shift start times for each row.
- Observed behavior
- The desired result is 7:00 AM for S1, 3:00 PM for S2, 11:00 PM for S3, and a blank cell for other or empty codes.
Confirm that the shift code for the first data row is in cell J3 and that the start-time result should appear in C3. Format column C as a Time value after entering the formula.
Use the IFS Formula to Assign Each Shift Start Time
Use this method when your Excel version supports the IFS function and you want a clear formula for multiple shift codes.
The formula checks the code in column J and returns an actual time value, which can be used in later calculations such as shift duration or attendance tracking.
Click cell C3, the cell where you want the start time for the shift code in J3.
Type or paste =IFS(J3="S1",TIME(7,0,0),J3="S2",TIME(15,0,0),J3="S3",TIME(23,0,0),TRUE,"") and press Enter.
Select C3 and drag the fill handle down, or double-click the fill handle, to apply the formula to the remaining rows in column C.
Select column C, then use Home > Number Format and choose Time. Use a custom format such as h:mm AM/PM if you want to show AM and PM.
Use Nested IF When IFS Is Not Available
Use this alternative if your Excel version does not recognize the IFS function.
Assign Shift Start Times Automatically in WPS Spreadsheets
WPS Spreadsheets can use the same IFS and TIME formula structure to translate shift codes into usable start-time values. Enter the formula once, fill it down the column, and format the results as time for a clean shift schedule.
- 1. Open the shift schedule: Open your workbook in WPS Spreadsheets and verify that shift codes are listed in column J.
- 2. Enter the formula in C3: Select C3 and enter =IFS(J3="S1",TIME(7,0,0),J3="S2",TIME(15,0,0),J3="S3",TIME(23,0,0),TRUE,"").
- 3. Fill the formula down: Select C3 and drag or double-click its fill handle to calculate start times for additional shift rows.
- 4. Set the time display: Select column C, go to Home > Number Format, and choose Time or set a custom h:mm AM/PM format.
Frequently Asked Questions
Why does the formula return a number instead of a time?
Spreadsheet programs store times as numeric values. Select the result cells in column C and use Home > Number Format > Time to display the values as times.
What happens if column J contains a code other than S1, S2, or S3?
The final TRUE,"" condition returns a blank cell for any unmatched code, including an empty shift-code cell.
Can I change the shift start times in the formula?
Yes. Edit the TIME(hour,minute,second) values. For example, replace TIME(7,0,0) with TIME(6,30,0) to return 6:30 AM for S1.
How do I apply the shift time formula to every row?
Enter the formula in C3, then drag the fill handle at the bottom-right corner of C3 down the column, or double-click it when adjacent data is continuous.
What formula should I use if IFS is unavailable?
Use =IF(J3="S1",TIME(7,0,0),IF(J3="S2",TIME(15,0,0),IF(J3="S3",TIME(23,0,0),""))) in C3, then fill it down.
