I have data that I export to Excel where the total time comes in as :mm:ss.
I need to convert that format into seconds. For example, my report shows me
:01:25, I need to be able to show that at 85 seconds. I have a formula that
will convert mm:ss to seconds which is =INT(G2)*60+((G2-INT(G2))*100) where
G2 is 01:25 the result will be 85. but the report i have now comes with an
extra :. I cannot seem to be able to automatically remove the : at the
begining of the expression or modify my formula above to account for the
extra :. I am sure this is pretty simple it is just beyone me though. Any
help will be much appreciated!
Phredd

Your formula won't work for 01:25 (try it!), but it would give a result of
85 if the G2 had contained 01.25 instead of 01:25.

For your text input of :01:25, you need =60*MID(G2,2,2)+RIGHT(G2,2) or
otherwise =RIGHT(G2,5)*24*60
--
David Biddulph

"Phredd" wrote in message
...
I have data that I export to Excel where the total time comes in as :mm:ss.
I need to convert that format into seconds. For example, my report shows
me
:01:25, I need to be able to show that at 85 seconds. I have a formula
that
will convert mm:ss to seconds which is =INT(G2)*60+((G2-INT(G2))*100)
where
G2 is 01:25 the result will be 85. but the report i have now comes with
an
extra :. I cannot seem to be able to automatically remove the : at the
begining of the expression or modify my formula above to account for the
extra :. I am sure this is pretty simple it is just beyone me though.
Any
help will be much appreciated!
Phredd

Try this...

=86400*("00"&G2)

where 86400 is the number of seconds in a year and, when the concatenation
is multiplied by it, the concatenation is turned into an actual time value
(because your value has a time format except for the missing hours part...
which the concatenation puts in).

Rick


"Phredd" wrote in message
...
I have data that I export to Excel where the total time comes in as :mm:ss.
I need to convert that format into seconds. For example, my report shows
me
:01:25, I need to be able to show that at 85 seconds. I have a formula
that
will convert mm:ss to seconds which is =INT(G2)*60+((G2-INT(G2))*100)
where
G2 is 01:25 the result will be 85. but the report i have now comes with
an
extra :. I cannot seem to be able to automatically remove the : at the
begining of the expression or modify my formula above to account for the
extra :. I am sure this is pretty simple it is just beyone me though.
Any
help will be much appreciated!
Phredd

In a day, rather than in a year, I hope, Rick?
--
David Biddulph

"Rick Rothstein (MVP - VB)" wrote in
message ...
Try this...

=86400*("00"&G2)

where 86400 is the number of seconds in a year and, when the concatenation
is multiplied by it, the concatenation is turned into an actual time value
(because your value has a time format except for the missing hours part...
which the concatenation puts in).

Rick


"Phredd" wrote in message
...
I have data that I export to Excel where the total time comes in as
:mm:ss.
I need to convert that format into seconds. For example, my report shows
me
:01:25, I need to be able to show that at 85 seconds. I have a formula
that
will convert mm:ss to seconds which is =INT(G2)*60+((G2-INT(G2))*100)
where
G2 is 01:25 the result will be 85. but the report i have now comes with
an
extra :. I cannot seem to be able to automatically remove the : at the
begining of the expression or modify my formula above to account for the
extra :. I am sure this is pretty simple it is just beyone me though.
Any
help will be much appreciated!
Phredd

When you get older, like me, time appears to go faster... it **seemed** like
a year to me<g; but yes, you are right, there are 86400 seconds in a
**day**, not a year (of course, the formula still works correctly, in spite
of this misstatement in my attempt to explain why). Thanks for noting that.

Rick


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
In a day, rather than in a year, I hope, Rick?
--
David Biddulph

"Rick Rothstein (MVP - VB)" wrote in
message ...
Try this...

=86400*("00"&G2)

where 86400 is the number of seconds in a year and, when the
concatenation is multiplied by it, the concatenation is turned into an
actual time value (because your value has a time format except for the
missing hours part... which the concatenation puts in).

Rick


"Phredd" wrote in message
...
I have data that I export to Excel where the total time comes in as
:mm:ss.
I need to convert that format into seconds. For example, my report
shows me
:01:25, I need to be able to show that at 85 seconds. I have a formula
that
will convert mm:ss to seconds which is =INT(G2)*60+((G2-INT(G2))*100)
where
G2 is 01:25 the result will be 85. but the report i have now comes with
an
extra :. I cannot seem to be able to automatically remove the : at the
begining of the expression or modify my formula above to account for the
extra :. I am sure this is pretty simple it is just beyone me though.
Any
help will be much appreciated!
Phredd

Rick,

Thank you so much for your help that worked great! I did discover another
problem i could use anyones' help with related to the same thing. Some of my
data is reflected as h:mm:ss, (which i didn't discover last night) and the
formula you provided to me returns a result of #VALUE for those cells. is
there a way also capture the h: also in a ss format? for example 1:01:23 as
3683.

Phredd.

"Rick Rothstein (MVP - VB)" wrote:

Try this...

=86400*("00"&G2)

where 86400 is the number of seconds in a year and, when the concatenation
is multiplied by it, the concatenation is turned into an actual time value
(because your value has a time format except for the missing hours part...
which the concatenation puts in).

Rick


"Phredd" wrote in message
...
I have data that I export to Excel where the total time comes in as :mm:ss.
I need to convert that format into seconds. For example, my report shows
me
:01:25, I need to be able to show that at 85 seconds. I have a formula
that
will convert mm:ss to seconds which is =INT(G2)*60+((G2-INT(G2))*100)
where
G2 is 01:25 the result will be 85. but the report i have now comes with
an
extra :. I cannot seem to be able to automatically remove the : at the
begining of the expression or modify my formula above to account for the
extra :. I am sure this is pretty simple it is just beyone me though.
Any
help will be much appreciated!
Phredd

My fault... I should not have used two zeroes in the concatenation... one
zero would have been enough for your original problem AND would also work
for the entries you just wrote about as long as you NEVER more than 9 in the
hours position (that is, either an entry like your original post showed or,
if an hour is present, the time value is 9:59:59 or less).

=86400*("0"&G2)

Rick


"Phredd" wrote in message
...
Rick,

Thank you so much for your help that worked great! I did discover another
problem i could use anyones' help with related to the same thing. Some of
my
data is reflected as h:mm:ss, (which i didn't discover last night) and
the
formula you provided to me returns a result of #VALUE for those cells. is
there a way also capture the h: also in a ss format? for example 1:01:23
as
3683.

Phredd.

"Rick Rothstein (MVP - VB)" wrote:

Try this...

=86400*("00"&G2)

where 86400 is the number of seconds in a year and, when the
concatenation
is multiplied by it, the concatenation is turned into an actual time
value
(because your value has a time format except for the missing hours
part...
which the concatenation puts in).

Rick


"Phredd" wrote in message
...
I have data that I export to Excel where the total time comes in as
:mm:ss.
I need to convert that format into seconds. For example, my report
shows
me
:01:25, I need to be able to show that at 85 seconds. I have a formula
that
will convert mm:ss to seconds which is =INT(G2)*60+((G2-INT(G2))*100)
where
G2 is 01:25 the result will be 85. but the report i have now comes
with
an
extra :. I cannot seem to be able to automatically remove the : at the
begining of the expression or modify my formula above to account for
the
extra :. I am sure this is pretty simple it is just beyone me though.
Any
help will be much appreciated!
Phredd

If you want to cope both with 1:01:23 and :01:23, try
=IF(LEN(A2)6,A2,RIGHT(A2,5)/60)*24*3600
--
David Biddulph

"Phredd" wrote in message
...
Rick,

Thank you so much for your help that worked great! I did discover another
problem i could use anyones' help with related to the same thing. Some of
my
data is reflected as h:mm:ss, (which i didn't discover last night) and
the
formula you provided to me returns a result of #VALUE for those cells. is
there a way also capture the h: also in a ss format? for example 1:01:23
as
3683.

Phredd.

"Rick Rothstein (MVP - VB)" wrote:

Try this...

=86400*("00"&G2)

where 86400 is the number of seconds in a year and, when the
concatenation
is multiplied by it, the concatenation is turned into an actual time
value
(because your value has a time format except for the missing hours
part...
which the concatenation puts in).

Rick


"Phredd" wrote in message
...
I have data that I export to Excel where the total time comes in as
:mm:ss.
I need to convert that format into seconds. For example, my report
shows
me
:01:25, I need to be able to show that at 85 seconds. I have a formula
that
will convert mm:ss to seconds which is =INT(G2)*60+((G2-INT(G2))*100)
where
G2 is 01:25 the result will be 85. but the report i have now comes
with
an
extra :. I cannot seem to be able to automatically remove the : at the
begining of the expression or modify my formula above to account for
the
extra :. I am sure this is pretty simple it is just beyone me though.
Any
help will be much appreciated!
Phredd

Rick,

Thanks, both you and David are awesome! Thank you so much. You guys are
Excel Rock Stars!

Phredd.

"Rick Rothstein (MVP - VB)" wrote:

My fault... I should not have used two zeroes in the concatenation... one
zero would have been enough for your original problem AND would also work
for the entries you just wrote about as long as you NEVER more than 9 in the
hours position (that is, either an entry like your original post showed or,
if an hour is present, the time value is 9:59:59 or less).

=86400*("0"&G2)

Rick


"Phredd" wrote in message
...
Rick,

Thank you so much for your help that worked great! I did discover another
problem i could use anyones' help with related to the same thing. Some of
my
data is reflected as h:mm:ss, (which i didn't discover last night) and
the
formula you provided to me returns a result of #VALUE for those cells. is
there a way also capture the h: also in a ss format? for example 1:01:23
as
3683.

Phredd.

"Rick Rothstein (MVP - VB)" wrote:

Try this...

=86400*("00"&G2)

where 86400 is the number of seconds in a year and, when the
concatenation
is multiplied by it, the concatenation is turned into an actual time
value
(because your value has a time format except for the missing hours
part...
which the concatenation puts in).

Rick


"Phredd" wrote in message
...
I have data that I export to Excel where the total time comes in as
:mm:ss.
I need to convert that format into seconds. For example, my report
shows
me
:01:25, I need to be able to show that at 85 seconds. I have a formula
that
will convert mm:ss to seconds which is =INT(G2)*60+((G2-INT(G2))*100)
where
G2 is 01:25 the result will be 85. but the report i have now comes
with
an
extra :. I cannot seem to be able to automatically remove the : at the
begining of the expression or modify my formula above to account for
the
extra :. I am sure this is pretty simple it is just beyone me though.
Any
help will be much appreciated!
Phredd

David,

Thank you so much for your help. I am just amazed at how much knowledge is
out there. Thanks for sharing. I wish I could buy you both a drink for your
help.

Phredd.

"David Biddulph" wrote:

If you want to cope both with 1:01:23 and :01:23, try
=IF(LEN(A2)6,A2,RIGHT(A2,5)/60)*24*3600
--
David Biddulph

"Phredd" wrote in message
...
Rick,

Thank you so much for your help that worked great! I did discover another
problem i could use anyones' help with related to the same thing. Some of
my
data is reflected as h:mm:ss, (which i didn't discover last night) and
the
formula you provided to me returns a result of #VALUE for those cells. is
there a way also capture the h: also in a ss format? for example 1:01:23
as
3683.

Phredd.

"Rick Rothstein (MVP - VB)" wrote:

Try this...

=86400*("00"&G2)

where 86400 is the number of seconds in a year and, when the
concatenation
is multiplied by it, the concatenation is turned into an actual time
value
(because your value has a time format except for the missing hours
part...
which the concatenation puts in).

Rick


"Phredd" wrote in message
...
I have data that I export to Excel where the total time comes in as
:mm:ss.
I need to convert that format into seconds. For example, my report
shows
me
:01:25, I need to be able to show that at 85 seconds. I have a formula
that
will convert mm:ss to seconds which is =INT(G2)*60+((G2-INT(G2))*100)
where
G2 is 01:25 the result will be 85. but the report i have now comes
with
an
extra :. I cannot seem to be able to automatically remove the : at the
begining of the expression or modify my formula above to account for
the
extra :. I am sure this is pretty simple it is just beyone me though.
Any
help will be much appreciated!
Phredd