# Formula to Convert 58:50 to 58:50:00

#### squeakums

##### Well-known Member
I need a formula or macro that will convert a sample format of 58:50 to 58:50:00. Is there one? I thought about =58:50*100 or whatever the cell number is * 100. But, then it changes it into a slighly different number.

Thanks,

Squeakums

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
How was your number entered? You could check the number format to see if that would get you what you want. Try changing it to [h]:mm:ss

Aren't 58:50 and 58:50:00 the same thing? It's just a matter of formatting to show seconds:

If your 58:50 is formatted as mm:ss and you want to change to 58:50:00 representing 58 hours and 50 minutes then multiply by 60 and format result cell as [h]:mm:ss

Here's the problem - I am converting from a file where it created mm:ss for some people and hh:mm:ss for others - have another formula =MIN((MOD(C3-B3,1)-D3)*24,14) - that is taking logged in and out and lunch - and I need it to take the accurate lunch time - and it won't do it if the format is mm:ss. I can use the A2/60 but then it will change the hh:mm:ss format as well. So, I need maybe an if/then formula that will only find the format mm:ss and convert to hh:mm:ss - example 58:00 to 00:58:00. Or am I going about this the wrong way?

I'm not to keen on the time formatting but ins't it based off of 0 and 1 for a 24 hour day.
I think it would have do to if your time, non-formatted, is <1 then you would want to adjust and leave it alone if it is >1. If(A1<1,A1/60,A1) or something along those lines.

That would almost work except the format that it is in is"

Calculation
01:00:03
01:00:54
01:03:27

58:46
01:00:01
01:01:01

01:00:06
01:00:01
56:07
59:19

So, if less than 1 then a1/60. It won't calculate less than one because those others are 59 - greater than 1. See?

In VBA you need less than TimeSerial(1,0,0). In a worksheet it would be less than TIME(1,0,0).

Perhaps you need to check whether the value is, say, less than 3 hours, then there could only be confusion if lunch is less than 3 minutes or more than 3 hours, e.g.

=MIN((MOD(C3-B3,1)-D3/IF(D3*24<3,1,60))*24,14)

That just addresses D3, I assume B3 and C3 are OK?

Wow, that is awesome!! The =MIN((MOD(C5-B5,1)-D5/IF(D5*24<3,1,60))*24,14) solves all of my problems. Thanks a bunch!

Replies
3
Views
116
Replies
8
Views
218
Replies
1
Views
317
Replies
1
Views
195
Replies
6
Views
564

1,203,453
Messages
6,055,530
Members
444,794
Latest member
HSAL

### 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.

### Which adblocker are you using?

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

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