How to Convert Date String To Date Time in Excel

Why This Task Matters in Excel

Excel is frequently used as the “last mile” tool for reporting, scheduling, forecasting, and data cleansing. Raw data rarely arrives in the perfect format you need, and date-time fields are among the most error-prone. Systems spit out timestamps like 20231207 143255, 12-07-2023 14:32, or 2023/12/07T14:32:55. Until Excel recognizes those strings as real date-time values, you cannot:

• Use them on a chart axis to create time-series visuals
• Filter or group by month, quarter, or hour in a PivotTable
• Measure elapsed time between events with subtraction
• Feed them into dashboard formulas such as NETWORKDAYS or FORECAST

Consider a logistics manager who downloads GPS pings from trucks every two minutes. The exported CSV contains “20231207 1432” instead of a valid date-time. Without converting that string, she cannot calculate idle time, pickup delays, or visualize routes by hour.

Finance teams confront the same issue while reconciling trades from multiple platforms, each with its own timestamp flavor. Marketing analysts joining web analytics (HH:MM:SS) to CRM data (MM/DD/YYYY HH:MM) also need consistent date-time types. Healthcare, manufacturing, scientific research—the examples are everywhere.

Excel excels (pun intended) at this task because its serial-number date system lets you treat date-times as regular numbers: integer days plus fractional time. Once converted, you have a single, language-independent representation that works across formulas, charts, and VBA. Failing to convert strings forces error-prone text hacks, breaks aggregations, and makes reports look unprofessional.

Finally, mastering this conversion builds foundational skills—text parsing, error checking, and data validation—that translate to many other data-wrangling chores. Learning it once pays dividends in every Excel workflow that touches external data.

Best Excel Approach

The most universally effective technique is to split the incoming text into its date and time components, feed each into DATEVALUE and TIMEVALUE, then add them together. DATEVALUE converts a recognizable date string to the integer portion of Excel’s serial number, while TIMEVALUE converts a recognizable time string to a decimal less than 1. Adding the two yields a full date-time value.

Syntax for the combined method:

=DATEVALUE(date_text) + TIMEVALUE(time_text)

Why choose this approach?

• Flexibility: Works for a wide range of “YYYY-MM-DD HH:MM:SS”, “MM/DD/YYYY HH:MM”, or even “07-12-2023 2:32 PM” once you isolate pieces.
• Transparency: Each part is visible for auditing, unlike complicated nested substitutions.
• No VBA or add-ins required, so it runs on any desktop, web, or Mac version of Excel.

When the incoming string has no delimiter between date and time (for example 20231207 143255), you can still use this strategy by inserting delimiters using TEXT functions such as LEFT, MID, and RIGHT before feeding DATEVALUE and TIMEVALUE.

Alternative quick hits exist—VALUE on its own, the Text to Columns wizard, Power Query—but they each have situational limits. The “DATEVALUE + TIMEVALUE” pattern is the Swiss-army knife that always works if you give it clean inputs.

Parameters and Inputs

Before diving in, confirm four things about your source column:

1. Date partly recognizable? DATEVALUE understands many regional patterns like 12/07/2023, 2023-12-07, or 7-Dec-2023. If the piece is exotic (e.g., 20231207), you must reformat it first.
2. Time partly recognizable? TIMEVALUE parses “14:32”, “2:32 PM”, or “14:32:55”. Missing colons must be inserted.
3. Data type: Inputs reside in text cells, not already dates. Mixed types cause INT or VALUE errors.
4. Consistency: Every record should match the expected pattern, or wrap the formula in IFERROR or LET for branching.

Optional parameters include:

• Custom delimiter positions for LEFT/MID/RIGHT extraction
• Locale-specific date parsing via DATE(year, month, day) if DATEVALUE fails
• Text trimming to remove hidden spaces or non-printing characters with TRIM and CLEAN

Edge cases: midnight records such as “00:00”, day roll-overs, or milliseconds. Excel stores only whole seconds; milliseconds require alternative handling like splitting into an integer and dividing by 86400000.

Step-by-Step Examples

Example 1: Basic Scenario

Scenario: You receive a call log where column A contains “12/07/2023 14:32”. This looks correct to humans but Excel keeps it as text, left-aligned and unusable.

1. Inspect the cell: Click [A2]. In the Formula Bar the string appears exactly as “12/07/2023 14:32”.
2. Split the string:
   • Date part: =LEFT(A2,10) returns “12/07/2023”
   • Time part: =MID(A2,12,5) returns “14:32”
3. Convert each part:

=DATEVALUE(LEFT(A2,10))

returns 45253 (serial for 7 Dec 2023).

=TIMEVALUE(MID(A2,12,5))

returns 0.605555556 (because 14:32 equals 14.533 hours divided by 24).
4. Combine:

=DATEVALUE(LEFT(A2,10)) + TIMEVALUE(MID(A2,12,5))

Result: 45253.60555556 displayed as 12/07/2023 14:32 once you apply a custom format like mm/dd/yyyy hh:mm.

Why it works: Excel dates are merely day counts since 1 Jan 1900. The time fraction slots in after the decimal point, so addition is the natural operation.

Common variations: Some logs use a single space or “T” separator. Adjust the MID starting number accordingly. If you omit seconds, TIMEVALUE still works. Should DATEVALUE misinterpret day-month order in non-US locales, use the DATE function instead:

=DATE(VALUE(MID(A2,7,4)),VALUE(LEFT(A2,2)),VALUE(MID(A2,4,2))) + TIMEVALUE(MID(A2,12,5))

Troubleshooting tips:

• If the result shows as a five-digit integer, apply a date-time format.
• If you get #VALUE!, check for trailing spaces—wrap the text in TRIM.
• Ensure the text truly contains a leading zero for single-digit months; inconsistent lengths break LEFT/MID offsets.

Example 2: Real-World Application

A fulfillment center downloads pallet sensor data in CSV. Column A is “20231207 143255” (no delimiters, seconds included). They must calculate the average temperature per hour.

1. Insert helper columns or a single LET formula. For clarity, we’ll show the helper columns approach first.

   • Year: =LEFT(A2,4) → “2023”
   • Month: =MID(A2,5,2) → “12”
   • Day: =MID(A2,7,2) → “07”
   • Hour: =MID(A2,10,2) → “14”
   • Minute: =MID(A2,12,2) → “32”
   • Second: =RIGHT(A2,2) → “55”

2. Convert date with DATE:

=DATE(LEFT(A2,4), MID(A2,5,2), MID(A2,7,2))

returns 45253.

3. Convert time to fraction:

=(MID(A2,10,2)/24) + (MID(A2,12,2)/1440) + (RIGHT(A2,2)/86400)

Explanation:

• Divide hours by 24
• Divide minutes by 1440 (24*60)
• Divide seconds by 86400 (246060)

4. Combine:

=DATE(LEFT(A2,4), MID(A2,5,2), MID(A2,7,2)) +
 (MID(A2,10,2)/24) + (MID(A2,12,2)/1440) + (RIGHT(A2,2)/86400)

5. Wrap in LET for readability and performance:

=LET(
 str,A2,
 yr,LEFT(str,4),
 mo,MID(str,5,2),
 dy,MID(str,7,2),
 hr,MID(str,10,2),
 mn,MID(str,12,2),
 sc,RIGHT(str,2),
 DATE(yr,mo,dy) + hr/24 + mn/1440 + sc/86400
)

Now the warehouse analyst can use a PivotTable to group by “hour” and compute averages.

Integration with other Excel features: Because the timestamp is a real date-time, you can build a Dynamic Array formula like:

=UNIQUE(INT(B2:B10000))

to list each unique day, or chart temperature trending.

Performance considerations: On 100 000 rows, complex string parsing can lag. The LET approach computes intermediate results once, improving speed roughly 25 percent over repeated MID calls in older Excel versions without implicit caching.

Example 3: Advanced Technique

Edge case: Logs arriving from a Unix-based log collector include timezone information such as 2023-12-07T14:32:55Z or 2023-12-07T14:32:55+02:00. You need them in local time.

1. Strip the T and timezone first:

=SUBSTITUTE(LEFT(A2,19),"T"," ")

returns “2023-12-07 14:32:55”.

2. Convert to serial:

=DATEVALUE(LEFT(B2,10)) + TIMEVALUE(MID(B2,12,8))

(Assume step 1 output placed in B2 via a helper cell or nested.)

3. Adjust timezone: If the original string ends with “Z” (UTC) and you are two hours ahead, add two hours:

= (DATEVALUE(LEFT(B2,10)) + TIMEVALUE(MID(B2,12,8))) + (2/24)

4. Automate detection:

=LET(
 txt,A2,
 base,DATEVALUE(LEFT(txt,10))+TIMEVALUE(MID(txt,12,8)),
 offset_text,IF(RIGHT(txt,1)="Z","+00:00",RIGHT(txt,6)),
 sign,IF(LEFT(offset_text,1)="+",1,-1),
 hr_offset,VALUE(MID(offset_text,2,2)),
 mn_offset,VALUE(RIGHT(offset_text,2)),
 base + sign*(hr_offset/24 + mn_offset/1440)
)

Professional tips:

• Use TEXTSPLIT (Excel 365) to separate on “T” and “+” or “Z” quickly.
• Store timezone offsets in a lookup table for daylight-saving transitions.
• Protect formulas with IFERROR around DATEVALUE, because some countries place the day before the month.

Performance optimization: Avoid repeated function calls on huge arrays—wrap expensive operations inside LET or convert once with Power Query, then cache.

Tips and Best Practices

1. Apply custom number formats like yyyy-mm-dd hh:mm:ss immediately after conversion so users do not mistake serial numbers for errors.
2. Trim and clean input strings with TRIM and CLEAN before parsing to eliminate invisible control characters from CSV exports.
3. When processing large datasets, stage conversion in Power Query to leverage its columnar engine, then load typed data back into the sheet.
4. Keep formulas in a “Staging” worksheet, shielding raw imports from manipulation and simplifying auditing.
5. Document delimiters (space, T, nothing) in cell comments or a data dictionary, so future team members understand offset positions.
6. Where possible, favor LET or LAMBDA to encapsulate logic; you gain speed and create reusable functions without VBA.

Common Mistakes to Avoid

1. Relying on VALUE alone. VALUE sometimes converts a simple “2023-12-07 14:32” to a serial, but fails on regional settings or missing seconds. Always test across locales.
2. Hard-coding offsets without considering variable string lengths; when months drop the leading zero (“7/4/2023”), LEFT and MID positions shift. Use FIND or SPLIT methods instead.
3. Forgetting to format the result. The five-digit integer scares users; apply a date-time format or wrap in TEXT for display.
4. Neglecting timezones. Subtracting timestamps from different zones yields negative or nonsensical durations; normalize first.
5. Breaking formulas during refresh. If new rows contain blank cells, DATEVALUE returns #VALUE!. Wrap calculations in IF(ISNUMBER(...)) or IFERROR for resilience.

Alternative Methods

When choosing, balance maintainability, performance, and automation requirements. You can migrate from a quick Text to Columns prototype to a robust formula or Power Query solution once patterns stabilize.

FAQ

When should I use this approach?

Use DATEVALUE + TIMEVALUE when you need a formula-driven, refreshable solution that can adapt to minor variations in incoming strings and automatically updates when new rows appear.

Can this work across multiple sheets?

Yes. Point your LEFT / MID / RIGHT or LET formula to another sheet’s range, or store the conversion as a named LAMBDA and call it across workbooks.

What are the limitations?

DATEVALUE depends on the system’s locale for ambiguous dates like “07/12/2023”. If month-day order is unpredictable, construct the date with DATE(year, month, day) to avoid misinterpretation. Excel also stores time only to the nearest second, so milliseconds are truncated unless you convert them to fractions manually.

How do I handle errors?

Wrap the entire expression in IFERROR, or isolate risky calls such as DATEVALUE. You can also use the new ERROR.TYPE and MAP functions (Excel 365) to log error reasons while allowing the rest of the dataset to calculate.

Does this work in older Excel versions?

Yes. Everything shown here works back to Excel 2007. LET and TEXTSPLIT are 365-only, but you can substitute helper columns or nested functions in older releases.

What about performance with large datasets?

On files above 50 000 rows, heavy text parsing can slow recalc. Techniques to improve speed:

• Use LET to compute one-time constants.
• Turn off Automatic Calculation during data loads.
• Push transformation to Power Query or a database when possible.
• Convert finished formulas to static values if historical data never changes.

Conclusion

Converting date strings to true date-time values unlocks Excel’s full analytical power—from time-series charts to duration calculations and sophisticated dashboards. By learning how to combine DATEVALUE, TIMEVALUE, and basic text functions, you create agile, refreshable workbooks that adapt to any timestamp format thrown at you. Master these techniques now, and the next time your boss hands you a messy CSV at 4 PM, you’ll breeze through cleanup, impress stakeholders, and free up time to deepen your Excel expertise in areas like Power Query, dynamic arrays, and real-time dashboards.