Read signed day-and-time interval text
Turn Oracle text like -00 00:30:00.000000 into a signed number of days.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | MID(A8,2,99) | Drops the + or - sign. LET calls the rest s. | "02 01:30:15.123456" |
| 2 | VALUE(TEXTBEFORE(s," ")) | Takes the days before the space. LET calls it d. | 2 |
| 3 | TIMEVALUE(LEFT(t,8)) | Reads the clock time as part of a day. | 0.062674 |
| 4 | VALUE(TEXTAFTER(t,"."))/86400000000 | Turns the 6 decimals into part of a day. | 0.0000014 |
| 5 | LET( … ) | Adds the parts and puts the sign back on all of them. | -2.062675 |
t is the text after the space (01:30:15.123456). The sign covers the whole time, so -00 00:30 stays negative.
=LET(s,MID(A8,2,99),d,VALUE(TEXTBEFORE(s," ")),t,TEXTAFTER(s," "),IF(LEFT(A8,1)="-",-1,1)*(d+TIMEVALUE(LEFT(t,8))+VALUE(TEXTAFTER(t,"."))/86400000000))How to use it
Yellow bar at the top? Click Enable Editing.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- Use your own intervals. Paste intervals like -02 01:30:15.123456 into the green column of the Template tab. The days fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Keep these results as plain numbers. Excel shows #### for a negative time in a time format.
Microsoft's help: TEXTBEFORE function
OTHER ROUTES YOU MIGHT LIKE

Convert year-month intervals to months
+02-03 → 27 months
Get the template →
Read timestamps wrapped in {ts}
{ts '2026-11-04 14:30:15'} → November 4, 2026 2:30:15 PM
Get the template →
Read /Date(…)/ values with a time zone
/Date(1793802615000+0100)/ → November 4, 2026 2:30:15 PM
Get the template →
Show an appointment time as 1:05 PM
13:05:00 → 1:05 PM
Take this route →