Forum Discussion

Lawrence_Lam_320's avatar
Lawrence_Lam_320
Copper Contributor
Jan 16, 2026

is it possible to capture HH:MM from the cell in text format DD-MM-YYYY HH:MM

Hi Expertise,  Need you help again. I need to capture the HH:MM from another cell DD-MM-YYYY HH:MM (text format) as below From 16-01-2026 00:30 (text format) To 24:30 (HH:MM format)   Regards L...
excel
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Jan 26, 2026

Calculating time durations that cross midnight can be tricky because Excel interprets "02:30" as a smaller number than "22:30", resulting in a negative value which Excel cannot display as time.

 

Extract the time (HH:MM) from a text string formatted as "DD-MM-YYYY HH:MM" and calculate the duration between two times that may span midnight.

 

Assumption

  • OT Start (text): DD-MM-YYYY HH:MM in A1
  • OT End (text): DD-MM-YYYY HH:MM in B1

=MOD(TIMEVALUE(MID(B1,12,5)) - TIMEVALUE(MID(A1,12,5)),1) * 24

Result is 4.

 

Explanation…

MID(cell,12,5) → extracts HH:MM

TIMEVALUE() → converts text to real Excel time

MOD(...,1) → handles midnight rollover automatically

*24 → converts time fraction to hours

 

If I may recommend...

Avoid keeping dates and times as text wherever possible.
Convert them to real date/time values first, then format for display.
This prevents calculation errors, especially when working across days.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Resources