Time Conversion Into Decimal Numbers w/Custom Format

[Disarmonious]

I have a column (D) where the user will be required to enter in minutes and seconds into cells (D5-D44). (I only want to work with minutes and seconds, no hours). All cells under this column are custom formatted to "00\:00", which auto-converts numbers typed in to minutes and seconds; For example, the user types "4545" which is converted to '45:45' (45 minutes and 45 seconds).

In another column (J), I have cells (J5-J44) that converts time into decimals. All cells under this column are formatted to 'Number' (00.00) with a formula "=TEXT(D5,"00\:00)+0". When I type "4545" to get '45:45' in cell D5, cell J5 converts it to 1.91? I was hoping to get "45.75". I don't understand why I'm getting "1.91", and would like to know how to get '45.75', the real decimal time for 45 minutes and 45 seconds instead of '1.91'?

 

[PatOBrien198]

try this formula

=FIXED(D5/100,0)+MOD(D5,100*FIXED(D5/100,0))/60

 

[shg]

Another way:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

45:45​

[/td][td]Format of A2: 00\:00[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td="bgcolor:#E5E5E5"]

45.75​

[/td][td]A3: =DOLLARDE(A2/100, 60)[/td][/tr]
[/table]

 

[Disarmonious]

try this formula

=FIXED(D5/100,0)+MOD(D5,100*FIXED(D5/100,0))/60

Awesome! Works like a charm! Thank you very much for your help!

Now that it works, I'm actually interested in how this formula (long formula that is) converts time into the correct decimal. I studied the formula, but it doesn't entirely make sense. If you have time, would you mind breaking this formula down for me?

 

[Disarmonious]

Another way:

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"]

A​

[/TD]
[TD="bgcolor: #C0C0C0"]

B​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

2​

[/TD]
[TD]

45:45​

[/TD]
[TD]Format of A2: 00\:00[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

3​

[/TD]
[TD="bgcolor: #E5E5E5"]

45.75​

[/TD]
[TD]A3: =DOLLARDE(A2/100, 60)[/TD]
[/TR]
</tbody>[/TABLE]

shg,

Woah! This way works as well! I appreciate it!

 

[shg]

You're welcome.

 

[PatOBrien198]

The value in the cell is actually 4545 (4,545) even if it is formatted with the colon in the middle.
The long formula first gets the hundreds value by dividing by 100 and dropping (fixing) the decimals.
Then it determines the value that is left over (MOD) when dividing the original value by the hundreds value (formula repeated) and
divides by 60 to make the decimal a portion of a minute.

Wow, that DollarDE formula is cool. I was not aware of that one.

 

[PatOBrien198]

You might want to go with the DollarDE formula.... the "long" formula does not work for values less than 1 minute, or with seconds entered above 60 seconds.