'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Automatically Display Today's Hours in Excel
Question details
The user needs a single cell in an Excel worksheet to automatically display the operating hours for the current day based on a weekly schedule list.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing a weekly schedule (e.g., library or business hours) and needing to dynamically show today's hours without manual daily updates.
- Observed behavior
- The user wants the designated cell to automatically update and display the correct daily hours whenever the current date changes.
Ensure you have a reference table set up in your worksheet containing the days of the week in one column and their corresponding operating hours in the adjacent column.
Use VLOOKUP and TODAY Functions
The most efficient way to dynamically pull today's hours from a reference list using built-in Excel formulas.
This method combines the TODAY function to get the current date, the TEXT function to convert it into a weekday name, and VLOOKUP to find the matching hours in your schedule list.
On a worksheet named 'List', enter the days of the week (Monday through Sunday) in cells A1 to A7, and their corresponding hours in cells B1 to B7.
Click on the cell in your main worksheet where you want today's operating hours to be displayed.
Type the formula =VLOOKUP(TEXT(TODAY(),"dddd"),List!A1:B7,2,FALSE) into the formula bar and press Enter.
The cell will now display the hours for the current day. This value will automatically update at midnight based on your computer's system clock.
Use a VBA Macro to Retrieve Today's Hours
Create a custom macro to look up today's weekday and write the corresponding hours to a specific cell, useful for automated reporting workflows.
Automate Your Schedules with WPS Spreadsheet
WPS Spreadsheet fully supports advanced formulas like VLOOKUP, TODAY, and TEXT, allowing you to easily automate your daily schedules. It provides a lightweight, highly compatible environment for all your data management tasks.
- 1. Set up your schedule list: Open WPS Spreadsheet and create a reference table with weekdays in column A and hours in column B.
- 2. Select your display cell: Click on the cell where you want today's hours to appear.
- 3. Apply the lookup formula: Type =VLOOKUP(TEXT(TODAY(),"dddd"),A1:B7,2,FALSE) and press Enter.
- 4. Enjoy automatic updates: The cell will instantly display the correct hours and automatically refresh every day without manual input.
Frequently Asked Questions
Why is my VLOOKUP formula returning an #N/A error?
This usually happens if the weekday name generated by the TEXT function doesn't exactly match the spelling in your reference list. Ensure there are no trailing spaces or typos in your list's weekday names.
Can I use XLOOKUP instead of VLOOKUP for this?
Yes. If you are using a newer version of Excel or WPS Spreadsheet, you can use =XLOOKUP(TEXT(TODAY(),"dddd"),List!A1:A7,List!B1:B7) to achieve the exact same result without needing to specify a column index number.
When exactly does the TODAY() function update?
The TODAY() function automatically updates to the current date based on your computer's system clock whenever the workbook is opened, or whenever the worksheet recalculates (like when you edit a cell).
Can I display the current date alongside the operating hours?
Yes, you can use the '&' operator to combine text strings. For example: =TEXT(TODAY(),"mm/dd/yyyy") & " Hours: " & VLOOKUP(TEXT(TODAY(),"dddd"),List!A1:B7,2,FALSE).
