Stop Google Sheets Changing Military Time

Updated 5 November 2026 · Standard formula, shown below

To stop Google Sheets changing military time, format the cells as plain text before entering or pasting values such as 1730, or enter a colon-formatted time such as 17:30 and apply a 24-hour time format. If Sheets has already interpreted a value as a time or date, changing the display format alone may not restore the original digits; you may need to re-enter or convert the data.

Why Sheets changes military time

Google Sheets tries to recognize what you type. A string like 17:30 is naturally interpreted as a time, and a string such as 1730 may be interpreted or displayed differently depending on the cell format, locale, and how it was entered. The underlying value can be a time serial number rather than the four-character code you intended.

Spreadsheet times are stored as fractions of a day: midnight is 0, noon is 0.5, and 6 p.m. is 0.75. A number format tells Sheets how to display that value. For example, a time may show as 5:30 PM under a 12-hour format or 17:30 under a 24-hour format. Formatting changes how a value appears; it does not necessarily change the stored value.

It also helps to distinguish two meanings of “military time.” In ordinary use, people often mean a 24-hour clock time written with a colon, such as 17:30. In some schedules, they mean a four-digit code without punctuation, such as 1730. The best fix depends on which one you need to preserve.

Keep four-digit entries such as 1730

Select the destination cells, column, or range before typing or pasting.
Choose Format > Number > Plain text.
Enter the values, for example 0730, 1200, and 1730. Leading zeros will remain part of the text.

Another option for a single entry is to type an apostrophe first, as in '1730. The apostrophe tells Sheets to treat the entry as text and is not displayed as part of the cell value. This is convenient for occasional entries, but preformatting a range is generally clearer for a column of schedule codes.

Plain text is appropriate when the digits are labels or codes that should not be used in time calculations. Text values do not behave like numeric time values in formulas, sorting, or time arithmetic. If you need to calculate elapsed time, use actual time values instead.

Display actual times on a 24-hour clock

If you want calculations to work and just want a 24-hour display, keep the values as times and change the number format:

Select the cells containing the times.
Choose Format > Number > Custom date and time.
Set a 24-hour pattern such as HH:mm, then apply it. If the format editor presents date and time components, remove unwanted date components and retain the hour and minute components.

In the usual spreadsheet format patterns, uppercase HH displays hours on a 24-hour clock and mm displays minutes. Thus a genuine time value for 5:30 p.m. can display as 17:30. By contrast, a 12-hour format may display it as 5:30 PM. A custom format affects appearance, not the time itself.

Use a colon when entering a time value, such as 17:30. This is less ambiguous than entering 1730 and hoping Sheets recognizes it as a time. If you need seconds, select a format that includes seconds, for example HH:mm:ss.

Fix values that have already changed

First inspect the cell contents and its number format. Select the cell and look at the formula bar: if it contains a time value while the grid shows a different display, the issue may be formatting rather than lost data. Try applying HH:mm if the value is a real time.

If Sheets converted a four-digit code into a time or date, setting the cell to Plain text afterward may not bring back the original code. Formatting does not reverse the earlier conversion. Undo immediately if the change just happened, or re-enter the source data after formatting the destination cells as Plain text. For a large import, prepare the destination column first, then paste or import the values as text where the import options allow it.

Be careful when rebuilding codes from actual times. A time value represents a fraction of a day, not a four-digit text code. A formula or conversion method must account for the intended format and leading zeros. Check results such as 0730 explicitly; a numeric display can otherwise drop the initial zero.

Locale and entry differences

Sheets’ interpretation of dates and numbers can depend on the spreadsheet’s locale, which is set under File > Settings. Locale differences are especially noticeable with dates and decimal separators, and can contribute to unexpected interpretations of ambiguous entries. If a sheet is shared across regions, agree on a consistent input convention and use explicit formatting rather than relying on automatic recognition.

Copying from another application can also bring formatting along with the values. If pasted entries change unexpectedly, try pasting into a preformatted Plain text range, or use a paste option that keeps values only when appropriate. Check a few sample cells before applying a fix to a full schedule.

Choose text or time values

Choose Plain text for fixed codes like 1730, employee or event identifiers, or any entry where exact characters—including leading zeros—must remain unchanged.
Choose a time value with a 24-hour format for clock times used in sorting, duration calculations, comparisons, or schedules that cross midnight.
Choose a duration format for elapsed time. A duration is not the same as a time of day: a shift lasting 27 hours should not wrap around and display as 03:00.

A common misconception is that changing a cell to Plain text will fix every value already altered. It only affects how new entries are interpreted and how existing values are treated as text; it cannot reliably reconstruct digits that were converted or discarded. Preserve an untouched copy of important imported data before making bulk changes.

Frequently Asked Questions

How do I make Google Sheets show 1730 instead of 5:30 PM?
For a code, format the cells as Plain text before entering or pasting it, then type 1730. If it is already a real time value, use a 24-hour display format such as HH:mm; that displays a colon, not the four-digit code.

How do I stop Sheets removing the zero from 0730?
Store 0730 as Plain text, either by formatting the cell first or by typing an apostrophe before the entry. Numeric values do not preserve leading zeros by default.

Can I use military time in formulas?
Yes. Enter actual times with a colon, such as 17:30, and format them as HH:mm. This keeps them usable as time values for calculations. Four-digit text codes such as 1730 are not equivalent to time values.

Why does 17:30 display as 5:30 PM?
The cell is using a 12-hour time format. Apply a 24-hour custom format such as HH:mm to change the display without changing the underlying time.

Why did changing the format not restore my original entry?
The original characters may already have been interpreted as a different value. Number formatting changes display, not the conversion that occurred when the value was entered. Undo, restore the original source, or re-enter it after setting the cells to Plain text.

Should I use 2400 for midnight?
As a time of day, midnight is normally represented as 00:00 in a 24-hour clock. Some schedules use 2400 informally to indicate the end of a day, but it is not the same cell value as midnight at the start of the next day. For calculations, use actual time values and make the date or day rollover explicit.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.