CODEWITHSUNDEEP
excelexcel-formula
Qi Xuan
Qi Xuan

Reputation: 43

Excel formula time subtract time in text format

I want to subtract the time in text format
like: 10.51 - 2.15 (means 10hrs 51min - 2hrs 15min)

Now I using Min-Min and then convert to Hrs in text format, but have any wonderful code that I no need convert to Min? just Hrs-Hrs. =CONCATENATE(INT((textMin)/60),".",MOD(INT(textMin),60))

I don't use time format or ":" because have a lot data to key in, number pad is good for key in much data.

Thanks a lot!

Upvotes: 0

Views: 189

Answers (1)

teylyn
teylyn

Reputation: 35915

Several ways to approach this.

  • Use Find and Replace to replace . with :, then use simple subtraction
  • leave the data as you input it and use a formula to convert the decimals into time values 
    =TIME(INT(A1),MOD(A1,1)*100,0)-TIME(INT(B1),MOD(B1,1)*100,0)

like here:

enter image description here

Or use

=SUBSTITUTE(A1,".",":")-SUBSTITUTE(B1,".",":")

Upvotes: 2

Related Questions