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

Hi Lawrence_Lam_320​,

Yes, you can capture the HH:MM portion from a text string like 16-01-2026 00:30.

If the format is always DD-MM-YYYY HH:MM, use: =RIGHT(A1,5) This will return 00:30.

If you’re on Excel 365 or newer, a cleaner option is: =TEXTAFTER(A1," ") This extracts everything after the space.

Important: Excel’s time system only goes up to 23:59. If you need to display 24:30 exactly as written, you’ll need to keep it as text (using RIGHT or TEXTAFTER). If you convert it to a real time value, Excel will roll it over to 00:30 of the next day.

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.

Hi Lorenzo, 

Thanks for your great help. I tried to use your formula in B1 and format cell B1 as [hh]:mm but i cannot get the correct result like yours. Actually, I need to calculate OT hours using OT Start time and End time. I have a problem when OT start time and end time are not in same day. The example like this. Could you please give me some advise? Thanks

OT Start Time : 22:30

OT End Time : 02:30

OT Hours : 4.00

Hi Lawrence_Lam_320​ 

The problem is you have a single date in "16-01-2026 00:30". So calculating cumulated hours as you expect is - in principle not possible - not making an assumption like below:

in B1 with you text value in A1:

=VALUE( A1 ) - ( DATEVALUE( A1 ) -1 )

and format B1 with Custom Number: [hh]:mm