SOLVED

Convert Time Text to Hours

%3CLINGO-SUB%20id%3D%22lingo-sub-2278124%22%20slang%3D%22en-US%22%3EConvert%20Time%20Text%20to%20Hours%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2278124%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20There%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20got%20about%202700%20rows%20of%20data.%20One%20of%20the%20columns%20was%20reported%20in%20time%20(ex.%201%20hour%206%20minutes).%20I'm%20looking%20for%20a%20way%20to%20convert%20this%20General%20text%20into%20Hours%20(ie.%201.10%20hours%20in%20the%20example%20above).%3C%2FP%3E%3CP%3EThank%20you%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2278124%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2278341%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Time%20Text%20to%20Hours%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2278341%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1030580%22%20target%3D%22_blank%22%3E%40KWalsh07%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%20this%20version%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3D24*TIMEVALUE(IF(ISNUMBER(FIND(%22hour%22%2CD2))%2CSUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2%2C%22s%22%2C%22%22)%2C%22%20hour%20%22%2C%22%3A%22)%2C%22%20minute%22%2C%22%3A00%22)%2C%220%3A%22%26amp%3BSUBSTITUTE(SUBSTITUTE(D2%2C%22s%22%2C%22%22)%2C%22%20minute%22%2C%22%3A00%22)))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2278301%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Time%20Text%20to%20Hours%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2278301%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%20How%20would%20modify%20that%20formula%20to%20automatically%20account%20for%20entries%20that%20read%20less%20that%20one%20hour%2C%20and%20more%20than%202%20hours%2C%20(Ex.%2057%20minutes%2C%20or%203%20hours%2027%20minutes)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2278264%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Time%20Text%20to%20Hours%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2278264%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOutstanding%2C%20thank%20you%20so%20much.%20You%20nailed%20it%20right%20on%20the%20head.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2278139%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Time%20Text%20to%20Hours%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2278139%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1030580%22%20target%3D%22_blank%22%3E%40KWalsh07%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20text%20values%20start%20in%20D2.%3C%2FP%3E%0A%3CP%3EFormat%20E2%20as%20a%20number%20with%202%20decimal%20places%20and%20enter%20the%20following%20formula%20in%20this%20cell%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3D24*TIMEVALUE(SUBSTITUTE(SUBSTITUTE(D2%2C%22%20hour%20%22%2C%22%3A%22)%2C%22%20minutes%22%2C%22%3A00%22))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi There,

 

I've got about 2700 rows of data. One of the columns was reported in time (ex. 1 hour 6 minutes). I'm looking for a way to convert this General text into Hours (ie. 1.10 hours in the example above).

Thank you in advance!

4 Replies

@KWalsh07 

Let's say the text values start in D2.

Format E2 as a number with 2 decimal places and enter the following formula in this cell:

 

=24*TIMEVALUE(SUBSTITUTE(SUBSTITUTE(D2," hour ",":")," minutes",":00"))

 

Fill down.

@Hans Vogelaar 

Outstanding, thank you so much. You nailed it right on the head.

@Hans Vogelaar How would modify that formula to automatically account for entries that read less that one hour, and more than 2 hours, (Ex. 57 minutes, or 3 hours 27 minutes)
best response confirmed by KWalsh07 (New Contributor)
Solution

@KWalsh07 

Try this version:

 

=24*TIMEVALUE(IF(ISNUMBER(FIND("hour",D2)),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"s","")," hour ",":")," minute",":00"),"0:"&SUBSTITUTE(SUBSTITUTE(D2,"s","")," minute",":00")))