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

[squeakums]

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

 

[IvenBach]

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

 

[barry houdini]

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

 

[squeakums]

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?

 

[IvenBach]

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.

 

[squeakums]

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?

 

[Andrew Poulsom]

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

 

[barry houdini]

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?

 

[squeakums]

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!