formula / math help

[TP]

I may be thick, but I need help reformatting imported data. The original
data is in mm:ss format, but when put in to Excel, 45:25 becomes 2725. If I
divide it by 60, I get 45.41667.

I need to maintain the integrity of the data on both a line-by-line basis
and when summed. So once I convert the following numbers to mm:ss, I
should be able to sum them and get 45:25


214
258
305
179
272
125
184
115
58
239
310
433
3
30

Thanks in advance!

Patti

 

[Frank Kabel]

Hi
could you explain a little bit more how the numbers from below should
be converted to mm:ss

 

[TP]

Hi Frank,

Here is data from the original source & how Excel displays it. Right now it
is just mm:ss, but it could also be hh:mm:ss - especially in the totals row.
I need to be able to do calculations on the individual rows.

(If you notice any discrepancies in the original data column, I probably
made a typo)

Thank you!

Original Data Display when pasted in Excel
:03:34 214
:04:18 258
:05:05 305
:02:59 179
:04:32 272
:02:05 125
:03:04 184
:01:55 115
:00:58 58
:03:59 239
:05:10 310
:07:13 433
:00:03 3
:00:30 30

 

[Peo Sjoblom]

For the totals use a custom format [hh]:mm:ss or if you want decimal hours
use general format and multiply with 24

 

[Dana DeLouis]

:03:34 214

Looks like the data was converted from mm:ss to just Minutes.
Divide 214 by 86400 to convert to a percentage of a day. Then format. It
will now total like you wish.
HTH.

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

For the totals use a custom format [hh]:mm:ss or if you want decimal hours
use general format and multiply with 24

--

Regards,

Peo Sjoblom

Hi Frank,

Here is data from the original source & how Excel displays it. Right

now

 

[Peo Sjoblom]

Hi Dana.

divide by 1440 if it's in minutes
--

Regards,

Peo Sjoblom

Looks like the data was converted from mm:ss to just Minutes.
Divide 214 by 86400 to convert to a percentage of a day. Then format. It
will now total like you wish.
HTH.

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

For the totals use a custom format [hh]:mm:ss or if you want decimal hours
use general format and multiply with 24

--

Regards,

Peo Sjoblom

Hi Frank,

Here is data from the original source & how Excel displays it. Right

now

it
is just mm:ss, but it could also be hh:mm:ss - especially in the

totals
row.

I need to be able to do calculations on the individual rows.

(If you notice any discrepancies in the original data column, I probably
made a typo)

Thank you!

Original Data Display when pasted in Excel
:03:34 214
:04:18 258
:05:05 305
:02:59 179
:04:32 272
:02:05 125
:03:04 184
:01:55 115
:00:58 58
:03:59 239
:05:10 310
:07:13 433
:00:03 3
:00:30 30

--------------------
:45:25 2725 (Totals)




Hi
could you explain a little bit more how the numbers from below should
be converted to mm:ss

--
Regards
Frank Kabel
Frankfurt, Germany


TP wrote:
I may be thick, but I need help reformatting imported data. The
original data is in mm:ss format, but when put in to Excel, 45:25
becomes 2725. If I divide it by 60, I get 45.41667.

I need to maintain the integrity of the data on both a line-by-line
basis and when summed. So once I convert the following numbers to
mm:ss, I should be able to sum them and get 45:25


214
258
305
179
272
125
184
115
58
239
310
433
3
30

Thanks in advance!

Patti

 

[Dana DeLouis]

Opps! Thanks. I did say that wrong. I think the 214 is the total number
of "Seconds" in 3:34 (3 Minutes, 34 Seconds)

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

Hi Dana.

divide by 1440 if it's in minutes
--

Regards,

Peo Sjoblom

:03:34 214

Looks like the data was converted from mm:ss to just Minutes.
Divide 214 by 86400 to convert to a percentage of a day. Then format. It
will now total like you wish.
HTH.

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

For the totals use a custom format [hh]:mm:ss or if you want decimal hours
use general format and multiply with 24

--

Regards,

Peo Sjoblom

Hi Frank,

Here is data from the original source & how Excel displays it.

Right
now

it
is just mm:ss, but it could also be hh:mm:ss - especially in the totals
row.
I need to be able to do calculations on the individual rows.

(If you notice any discrepancies in the original data column, I probably
made a typo)

Thank you!

Original Data Display when pasted in Excel
:03:34 214
:04:18 258
:05:05 305
:02:59 179
:04:32 272
:02:05 125
:03:04 184
:01:55 115
:00:58 58
:03:59 239
:05:10 310
:07:13 433
:00:03 3
:00:30 30

--------------------
:45:25 2725 (Totals)




Hi
could you explain a little bit more how the numbers from below should
be converted to mm:ss

--
Regards
Frank Kabel
Frankfurt, Germany


TP wrote:
I may be thick, but I need help reformatting imported data. The
original data is in mm:ss format, but when put in to Excel, 45:25
becomes 2725. If I divide it by 60, I get 45.41667.

I need to maintain the integrity of the data on both a line-by-line
basis and when summed. So once I convert the following numbers to
mm:ss, I should be able to sum them and get 45:25


214
258
305
179
272
125
184
115
58
239
310
433
3
30

Thanks in advance!

Patti

 

[TP]

Dana,

86400 did give me the correct result... thanks so much to all of you!!

Patti

Opps! Thanks. I did say that wrong. I think the 214 is the total number
of "Seconds" in 3:34 (3 Minutes, 34 Seconds)

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

Hi Dana.

divide by 1440 if it's in minutes
--

Regards,

Peo Sjoblom

:03:34 214

Looks like the data was converted from mm:ss to just Minutes.
Divide 214 by 86400 to convert to a percentage of a day. Then format. It
will now total like you wish.
HTH.

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


For the totals use a custom format [hh]:mm:ss or if you want decimal hours
use general format and multiply with 24

--

Regards,

Peo Sjoblom

Hi Frank,

Here is data from the original source & how Excel displays it. Right
now
it
is just mm:ss, but it could also be hh:mm:ss - especially in the totals
row.
I need to be able to do calculations on the individual rows.

(If you notice any discrepancies in the original data column, I probably
made a typo)

Thank you!

Original Data Display when pasted in Excel
:03:34 214
:04:18 258
:05:05 305
:02:59 179
:04:32 272
:02:05 125
:03:04 184
:01:55 115
:00:58 58
:03:59 239
:05:10 310
:07:13 433
:00:03 3
:00:30 30

--------------------
:45:25 2725 (Totals)




Hi
could you explain a little bit more how the numbers from below should
be converted to mm:ss

--
Regards
Frank Kabel
Frankfurt, Germany


TP wrote:
I may be thick, but I need help reformatting imported data. The
original data is in mm:ss format, but when put in to Excel, 45:25
becomes 2725. If I divide it by 60, I get 45.41667.

I need to maintain the integrity of the data on both a line-by-line
basis and when summed. So once I convert the following

numbers
to

mm:ss, I should be able to sum them and get 45:25


214
258
305
179
272
125
184
115
58
239
310
433
3
30

Thanks in advance!

Patti