Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

# Time conversion formula help

Copper Contributor

# Time conversion formula help

I am trying to convert a standard time sum formula from h:mm to h+mm and no matter how I input the hour won’t carry over to add the sum but the minutes do. I feel as though it is a simple fix but I am stumped and was hoping someone can point me in the right direction.

3 Replies

# Re: Time conversion formula help

Show your input and desired output.

# Re: Time conversion formula help

@Harun24HR sorry I guess I should’ve included an example. Say I have multiple “training times in an excel tracker such as 1:45 = an hour and 45 minutes. I would like the formula to accept 1+45 and add the sum off all different times. So 1:30 twice is 3:00 and I’m just trying to get to a point where I input 1+30 and 1+30= 3 in my sum box. Essentially just replacing the colon with the plus sign. No matter what I do it won’t accept the hour number in front of the + but it accepts the minutes behind the + sign. So if I input 1+00 it won’t register but if I input +45 it accepts the 45 into the formula and shows +45.

# Re: Time conversion formula help

@Holmyjj42 Give a try on below formula-

``=LET(x,TEXTSPLIT(TEXTAFTER(FORMULATEXT(A1),"="),"+"),TIME(INDEX(x,1,1),INDEX(x,1,2),0))``

See the attached file.