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

[bark01]

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

 

[Alex Blakenburg]

See if this works for you.

20220504 Large Time Conversion Test bark01.xlsx
	A	B
1	Original Value	Time Value
2	20000:30:00	20000:30:00
3
4	Time to Add (to test value)
5	99:15:00	99:15:00
6
7	Result
8	#VALUE!	20099:45:00
Time Conversion Test

Cell Formulas
Range	Formula
B2	B2	=LEFT(A2,FIND(":",A2)-1)/24+TIMEVALUE("00" & RIGHT(A2,FIND(":",A2)))
A8:B8	A8	=A2+A5