Convert large time as text (15000:00:00) to hours mins secs

bark01

Board Regular
Joined
Sep 6, 2006
Messages
132
Office Version
  1. 365
Platform
  1. Windows
I'm copy and pasting time values from an email to a spreadsheet.

The format of the spreadsheet is set to [H]:MM:SS

When i paste in some values i.e. 1425:20:58 excel recognises this a time value and stores it as 29/02/1904 09:20:58 which enables me to calculate from it.

When i paste in larger numbers i.e. 15000:00:00 excel doesn't recognise this as as time as pastes it in as text so i can't calculate from it. I've tried timevalue() but tis doesn't work.

Is there a way for me to get excel to recognize 15000:00:00 as a time or a formula i can use to get it into a time format?

I think I need to convert it into days and hours above 01/01/1904 so 15000:00:00 would be 625 days and 0 hours do i need the cell to show 17/09/1905 00:00:00
 
Last edited:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,038
Office Version
  1. 365
Platform
  1. Windows
See if this works for you.

20220504 Large Time Conversion Test bark01.xlsx
AB
1Original ValueTime Value
220000:30:0020000:30:00
3
4Time to Add (to test value)
599:15:0099:15:00
6
7Result
8#VALUE!20099:45:00
Time Conversion Test
Cell Formulas
RangeFormula
B2B2=LEFT(A2,FIND(":",A2)-1)/24+TIMEVALUE("00" & RIGHT(A2,FIND(":",A2)))
A8:B8A8=A2+A5
 
Solution

Forum statistics

Threads
1,175,770
Messages
5,899,404
Members
434,767
Latest member
ASB21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top