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" <(E-Mail Removed)> wrote in message
news:E8A02597-E1A9-4B4D-A540-(E-Mail Removed)...
>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)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:E8A02597-E1A9-4B4D-A540-(E-Mail Removed)...
>>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
news:4858ff13$(E-Mail Removed)...
> In a day, rather than in a year, I hope, Rick?
> --
> David Biddulph
>
> "Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
> message news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:E8A02597-E1A9-4B4D-A540-(E-Mail Removed)...
>>>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" <(E-Mail Removed)> wrote in message
> news:E8A02597-E1A9-4B4D-A540-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
news:30088D29-0FA7-49F9-86D4-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:E8A02597-E1A9-4B4D-A540-(E-Mail Removed)...
>> >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" <(E-Mail Removed)> wrote in message
news:30088D29-0FA7-49F9-86D4-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:E8A02597-E1A9-4B4D-A540-(E-Mail Removed)...
>> >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" <(E-Mail Removed)> wrote in message
> news:30088D29-0FA7-49F9-86D4-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:E8A02597-E1A9-4B4D-A540-(E-Mail Removed)...
> >> >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" <(E-Mail Removed)> wrote in message
> news:30088D29-0FA7-49F9-86D4-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:E8A02597-E1A9-4B4D-A540-(E-Mail Removed)...
> >> >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
> >>
> >>
>
>
>