Calculation Issues

Fix Excel Date Changing to Date and Time After Using Name Manager

Maira MehtabMaira Mehtab Nov 2, 2026 869 views

Question details

The user needs to fix an issue where an Excel date-only cell automatically converts to a date and time format and shifts backward by one hour when it is named or linked to document properties.

How to Fix Excel Date Changing to Date and Time After Using Name Manager
Product
Microsoft Excel
Device & OS
not provided
Scenario
Naming cells via Name Manager or linking them to custom document properties like Document_Date in an Excel workbook.
Observed behavior
The date value shifts backward by one hour (e.g., October 31 becomes October 30 at 11:00 PM) and displays as a date and time instead of the intended date-only format.
Before you start

Before troubleshooting this date formatting glitch, create a duplicate backup of your original workbook and save it to a local folder to ensure no data is corrupted during testing.

Solution 1Recommended

Isolate the Issue in a Local Environment

Determine if the date shift bug is caused by OneDrive synchronization, document caching, or the original workbook file by replicating the scenario locally.

Because cloud storage platforms like OneDrive occasionally experience caching delays or time zone mismatch issues during synchronization, testing the same actions on a purely local file is the best way to identify the root cause.

1
Create a New Local Workbook

Open Excel and press Ctrl+N to create a completely new workbook. Save this new workbook directly to your local hard drive (e.g., your Desktop or local Documents folder), ensuring it is outside of any OneDrive or cloud-synced directory.

2
Enter and Format the Date

Type a date into a blank cell and verify that the cell format is correctly set to 'Short Date' or 'Long Date' via the Home tab.

3
Use Name Manager

Navigate to the Formulas tab on the ribbon and click 'Name Manager'. Assign a new name to the cell containing your date.

4
Link Document Properties

Link the cell to custom document properties (such as Document_Date) and observe if the value shifts backward by an hour. If the local file behaves normally, the issue lies in your original file's cache or OneDrive sync settings.

Isolate the Issue in a Local Environment
Contact Support for Cloud Bugs: If the date conversion cannot be reproduced locally but persists in the OneDrive file, you may need to clear your Microsoft Office cache or submit the file to Microsoft support for further analysis.
Free Microsoft Office alternative

Try WPS Office to Avoid Unpredictable Sync Issues

If persistent caching or time zone sync bugs in Excel continue to disrupt your document properties, consider switching to WPS Office. It provides a highly compatible and stable environment for managing complex spreadsheets, Name Manager functions, and cell formatting.

  1. 1. Download and Install: Visit the official WPS Office website and download the free installation package for your operating system.
  2. 2. Open Your Excel File: Launch WPS Spreadsheet, go to Menu > Open, and select your existing .xlsx workbook to continue working seamlessly.
Fully compatible with Microsoft Excel file formats (.xlsx, .xls, .csv).Stable date formatting without unexpected daylight saving time glitches.Lightweight software offering smoother performance and faster load times.Familiar user interface ensuring a seamless migration with zero learning curve.
QA img-9

Frequently Asked Questions

Why does my Excel date change to a date and time format automatically?

This usually happens when a cell is linked to specific document properties (like Document_Date) that inherently store timestamps alongside dates, or when time zone sync discrepancies cause a midnight date value to shift backward into the previous day's hours.

How can I fix a date that shifted back by one hour in Excel?

A one-hour backward shift is typically caused by Daylight Saving Time (DST) mismatches. You can resolve this by ensuring your local Windows date/time settings and your cloud storage (OneDrive/SharePoint) regional settings are set to the exact same time zone.

What is the Name Manager in Excel used for?

The Name Manager is located under the Formulas tab and is used to create, edit, or delete descriptive names for cells, ranges, formulas, or constants. This makes complex formulas easier to read and maintain.