+ Reply to Thread
Results 1 to 10 of 10

Converting Minutes:Seconds to Minutes

  1. #1
    NickC
    Guest

    Converting Minutes:Seconds to Minutes

    I could download my telephone bill online.
    When I view the bill online The Duration of Call column which is listed in
    minutes appears as (for example)
    5:00 ..............(for 5 minutes)
    16:00.............(for 16 minutes)
    21:00.............(for 21 minutes)
    29:00.............(for 29 minutes)
    32:00.............(for 32 minutes)

    When I copy or download the bill this column appears as (Same example)
    5:00................(for 5 minutes)
    16:00.............(for 16 minutes)
    29:00:00.............(for 29 minutes)
    32:00:00.............(for 32 minutes)

    I would like to be able to do a total for the number of minutes for over 500
    calls listed in my bill. Do I need to change the format of the cells. When
    I look in the formulas bar, the 29:00:00 (29 minutes), I see 01-01-1900
    5:00:00 AM

    Thanks for help






  2. #2
    Bob Phillips
    Guest

    Re: Converting Minutes:Seconds to Minutes

    How do calls greater than 1 hour look?

    If you know you have none greater than 1 hour, you could in B1 use

    =MOD(A3,1)

    and sum these.

    doesn't the telephone company sum them for you?

    --
    HTH

    Bob Phillips

    "NickC" <[email protected]> wrote in message
    news:%[email protected]...
    > I could download my telephone bill online.
    > When I view the bill online The Duration of Call column which is listed in
    > minutes appears as (for example)
    > 5:00 ..............(for 5 minutes)
    > 16:00.............(for 16 minutes)
    > 21:00.............(for 21 minutes)
    > 29:00.............(for 29 minutes)
    > 32:00.............(for 32 minutes)
    >
    > When I copy or download the bill this column appears as (Same example)
    > 5:00................(for 5 minutes)
    > 16:00.............(for 16 minutes)
    > 29:00:00.............(for 29 minutes)
    > 32:00:00.............(for 32 minutes)
    >
    > I would like to be able to do a total for the number of minutes for over

    500
    > calls listed in my bill. Do I need to change the format of the cells.

    When
    > I look in the formulas bar, the 29:00:00 (29 minutes), I see 01-01-1900
    > 5:00:00 AM
    >
    > Thanks for help
    >
    >
    >
    >
    >




  3. #3
    NickC
    Guest

    Re: Converting Minutes:Seconds to Minutes

    Greater than one hour e.g 94:00:00 minutes
    and in the formula bar: 03-01-1900 10:00:00 PM


    "Bob Phillips" <[email protected]> wrote in message
    news:[email protected]...
    > How do calls greater than 1 hour look?
    >
    > If you know you have none greater than 1 hour, you could in B1 use
    >
    > =MOD(A3,1)
    >
    > and sum these.
    >
    > doesn't the telephone company sum them for you?
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > "NickC" <[email protected]> wrote in message
    > news:%[email protected]...
    > > I could download my telephone bill online.
    > > When I view the bill online The Duration of Call column which is listed

    in
    > > minutes appears as (for example)
    > > 5:00 ..............(for 5 minutes)
    > > 16:00.............(for 16 minutes)
    > > 21:00.............(for 21 minutes)
    > > 29:00.............(for 29 minutes)
    > > 32:00.............(for 32 minutes)
    > >
    > > When I copy or download the bill this column appears as (Same example)
    > > 5:00................(for 5 minutes)
    > > 16:00.............(for 16 minutes)
    > > 29:00:00.............(for 29 minutes)
    > > 32:00:00.............(for 32 minutes)
    > >
    > > I would like to be able to do a total for the number of minutes for over

    > 500
    > > calls listed in my bill. Do I need to change the format of the cells.

    > When
    > > I look in the formulas bar, the 29:00:00 (29 minutes), I see 01-01-1900
    > > 5:00:00 AM
    > >
    > > Thanks for help
    > >
    > >
    > >
    > >
    > >

    >
    >




  4. #4
    Bob Phillips
    Guest

    Re: Converting Minutes:Seconds to Minutes

    Nick,

    Try this formula in B1 and copy down, then sum the B's

    =IF(LEN(A1)-LEN(SUBSTITUTE(A1,":",""))=1,TIME(0,LEFT(A1,LEN(A1)-3),RIGHT(A1,
    2)),TIME(LEFT(A1,LEN(A1)-3),MID(A1,LEN(A1)-3,2),RIGHT(A1,2)))

    --
    HTH

    Bob Phillips

    "NickC" <[email protected]> wrote in message
    news:[email protected]...
    > Greater than one hour e.g 94:00:00 minutes
    > and in the formula bar: 03-01-1900 10:00:00 PM
    >
    >
    > "Bob Phillips" <[email protected]> wrote in message
    > news:[email protected]...
    > > How do calls greater than 1 hour look?
    > >
    > > If you know you have none greater than 1 hour, you could in B1 use
    > >
    > > =MOD(A3,1)
    > >
    > > and sum these.
    > >
    > > doesn't the telephone company sum them for you?
    > >
    > > --
    > > HTH
    > >
    > > Bob Phillips
    > >
    > > "NickC" <[email protected]> wrote in message
    > > news:%[email protected]...
    > > > I could download my telephone bill online.
    > > > When I view the bill online The Duration of Call column which is

    listed
    > in
    > > > minutes appears as (for example)
    > > > 5:00 ..............(for 5 minutes)
    > > > 16:00.............(for 16 minutes)
    > > > 21:00.............(for 21 minutes)
    > > > 29:00.............(for 29 minutes)
    > > > 32:00.............(for 32 minutes)
    > > >
    > > > When I copy or download the bill this column appears as (Same example)
    > > > 5:00................(for 5 minutes)
    > > > 16:00.............(for 16 minutes)
    > > > 29:00:00.............(for 29 minutes)
    > > > 32:00:00.............(for 32 minutes)
    > > >
    > > > I would like to be able to do a total for the number of minutes for

    over
    > > 500
    > > > calls listed in my bill. Do I need to change the format of the cells.

    > > When
    > > > I look in the formulas bar, the 29:00:00 (29 minutes), I see

    01-01-1900
    > > > 5:00:00 AM
    > > >
    > > > Thanks for help
    > > >
    > > >
    > > >
    > > >
    > > >

    > >
    > >

    >
    >




  5. #5
    NickC
    Guest

    Re: Converting Minutes:Seconds to Minutes

    Sorry but both these formulae give incorrect results. Any other idea


    "Bob Phillips" <[email protected]> wrote in message
    news:[email protected]...
    > Nick,
    >
    > Try this formula in B1 and copy down, then sum the B's
    >
    >

    =IF(LEN(A1)-LEN(SUBSTITUTE(A1,":",""))=1,TIME(0,LEFT(A1,LEN(A1)-3),RIGHT(A1,
    > 2)),TIME(LEFT(A1,LEN(A1)-3),MID(A1,LEN(A1)-3,2),RIGHT(A1,2)))
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > "NickC" <[email protected]> wrote in message
    > news:[email protected]...
    > > Greater than one hour e.g 94:00:00 minutes
    > > and in the formula bar: 03-01-1900 10:00:00 PM
    > >
    > >
    > > "Bob Phillips" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > How do calls greater than 1 hour look?
    > > >
    > > > If you know you have none greater than 1 hour, you could in B1 use
    > > >
    > > > =MOD(A3,1)
    > > >
    > > > and sum these.
    > > >
    > > > doesn't the telephone company sum them for you?
    > > >
    > > > --
    > > > HTH
    > > >
    > > > Bob Phillips
    > > >
    > > > "NickC" <[email protected]> wrote in message
    > > > news:%[email protected]...
    > > > > I could download my telephone bill online.
    > > > > When I view the bill online The Duration of Call column which is

    > listed
    > > in
    > > > > minutes appears as (for example)
    > > > > 5:00 ..............(for 5 minutes)
    > > > > 16:00.............(for 16 minutes)
    > > > > 21:00.............(for 21 minutes)
    > > > > 29:00.............(for 29 minutes)
    > > > > 32:00.............(for 32 minutes)
    > > > >
    > > > > When I copy or download the bill this column appears as (Same

    example)
    > > > > 5:00................(for 5 minutes)
    > > > > 16:00.............(for 16 minutes)
    > > > > 29:00:00.............(for 29 minutes)
    > > > > 32:00:00.............(for 32 minutes)
    > > > >
    > > > > I would like to be able to do a total for the number of minutes for

    > over
    > > > 500
    > > > > calls listed in my bill. Do I need to change the format of the

    cells.
    > > > When
    > > > > I look in the formulas bar, the 29:00:00 (29 minutes), I see

    > 01-01-1900
    > > > > 5:00:00 AM
    > > > >
    > > > > Thanks for help
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >




  6. #6
    Michael Bednarek
    Guest

    Re: Converting Minutes:Seconds to Minutes

    On Fri, 3 Jun 2005 05:42:43 -0400, "NickC" <[email protected]> wrote
    in microsoft.public.excel:

    >I could download my telephone bill online.
    >When I view the bill online The Duration of Call column which is listed in
    >minutes appears as (for example)
    >5:00 ..............(for 5 minutes)
    >16:00.............(for 16 minutes)
    >21:00.............(for 21 minutes)
    >29:00.............(for 29 minutes)
    >32:00.............(for 32 minutes)
    >
    >When I copy or download the bill this column appears as (Same example)
    >5:00................(for 5 minutes)
    >16:00.............(for 16 minutes)
    >29:00:00.............(for 29 minutes)
    >32:00:00.............(for 32 minutes)
    >
    >I would like to be able to do a total for the number of minutes for over 500
    >calls listed in my bill. Do I need to change the format of the cells. When
    >I look in the formulas bar, the 29:00:00 (29 minutes), I see 01-01-1900
    >5:00:00 AM


    Strangely, the following format seems to work: [h]:ss; it gives:
    5:00
    16:00
    21:00
    29:00
    32:00
    which Excel adds up to 103:00

    I suspect Excel treats the digits to the left of the colon as hours but,
    because of the square brackets, doesn't aggregate them into days; the
    digits to the right of the colon are then treated as minutes, which
    doesn't matter as both seconds and minutes have 60 in every major unit
    (sexagesimal system).

    To convert those 103 minutes to Hours:minutes divide the cell by 60 and
    display the result in the same format ([h]:ss).

    --
    Michael Bednarek http://mbednarek.com/ "POST NO BILLS"

  7. #7
    Forum Contributor
    Join Date
    06-10-2004
    Location
    India
    Posts
    1,066
    Hi Nick,

    After getting the values into excel, the data seems to be formatted as dates, and minutes as hours. Do the following:

    Assuming your minutes data is in A1 and down, enter the following formula in column B and copy down:
    =HOUR(A1)+DAY(A1)*24

    And then sum them using sum
    =SUM(B1:B10)

    Your example below gave me 82 minutes by using the above method.

    Mangesh





    When I copy or download the bill this column appears as (Same example)
    5:00................(for 5 minutes)
    16:00.............(for 16 minutes)
    29:00:00.............(for 29 minutes)
    32:00:00.............(for 32 minutes)

  8. #8
    Bob Phillips
    Guest

    Re: Converting Minutes:Seconds to Minutes

    Worked for me with data as you showed.

    --
    HTH

    Bob Phillips

    "NickC" <[email protected]> wrote in message
    news:[email protected]...
    > Sorry but both these formulae give incorrect results. Any other idea
    >
    >
    > "Bob Phillips" <[email protected]> wrote in message
    > news:[email protected]...
    > > Nick,
    > >
    > > Try this formula in B1 and copy down, then sum the B's
    > >
    > >

    >

    =IF(LEN(A1)-LEN(SUBSTITUTE(A1,":",""))=1,TIME(0,LEFT(A1,LEN(A1)-3),RIGHT(A1,
    > > 2)),TIME(LEFT(A1,LEN(A1)-3),MID(A1,LEN(A1)-3,2),RIGHT(A1,2)))
    > >
    > > --
    > > HTH
    > >
    > > Bob Phillips
    > >
    > > "NickC" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Greater than one hour e.g 94:00:00 minutes
    > > > and in the formula bar: 03-01-1900 10:00:00 PM
    > > >
    > > >
    > > > "Bob Phillips" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > How do calls greater than 1 hour look?
    > > > >
    > > > > If you know you have none greater than 1 hour, you could in B1 use
    > > > >
    > > > > =MOD(A3,1)
    > > > >
    > > > > and sum these.
    > > > >
    > > > > doesn't the telephone company sum them for you?
    > > > >
    > > > > --
    > > > > HTH
    > > > >
    > > > > Bob Phillips
    > > > >
    > > > > "NickC" <[email protected]> wrote in message
    > > > > news:%[email protected]...
    > > > > > I could download my telephone bill online.
    > > > > > When I view the bill online The Duration of Call column which is

    > > listed
    > > > in
    > > > > > minutes appears as (for example)
    > > > > > 5:00 ..............(for 5 minutes)
    > > > > > 16:00.............(for 16 minutes)
    > > > > > 21:00.............(for 21 minutes)
    > > > > > 29:00.............(for 29 minutes)
    > > > > > 32:00.............(for 32 minutes)
    > > > > >
    > > > > > When I copy or download the bill this column appears as (Same

    > example)
    > > > > > 5:00................(for 5 minutes)
    > > > > > 16:00.............(for 16 minutes)
    > > > > > 29:00:00.............(for 29 minutes)
    > > > > > 32:00:00.............(for 32 minutes)
    > > > > >
    > > > > > I would like to be able to do a total for the number of minutes

    for
    > > over
    > > > > 500
    > > > > > calls listed in my bill. Do I need to change the format of the

    > cells.
    > > > > When
    > > > > > I look in the formulas bar, the 29:00:00 (29 minutes), I see

    > > 01-01-1900
    > > > > > 5:00:00 AM
    > > > > >
    > > > > > Thanks for help
    > > > > >
    > > > > >
    > > > > >
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >




  9. #9
    Bob Phillips
    Guest

    Re: Converting Minutes:Seconds to Minutes

    That way would convert the 94:00:00 example he gave wrongly, the OP stated
    that was 1 hour 34 minutes in reality.

    --
    HTH

    Bob Phillips

    "mangesh_yadav" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > Hi Nick,
    >
    > After getting the values into excel, the data seems to be formatted as
    > dates, and minutes as hours. Do the following:
    >
    > Assuming your minutes data is in A1 and down, enter the following
    > formula in column B and copy down:
    > =HOUR(A1)+DAY(A1)*24
    >
    > And then sum them using sum
    > =SUM(B1:B10)
    >
    > Your example below gave me 82 minutes by using the above method.
    >
    > Mangesh
    >
    >
    >
    >
    >
    > >
    > > When I copy or download the bill this column appears as (Same example)
    > > 5:00................(for 5 minutes)
    > > 16:00.............(for 16 minutes)
    > > 29:00:00.............(for 29 minutes)
    > > 32:00:00.............(for 32 minutes)

    >
    >
    > --
    > mangesh_yadav
    > ------------------------------------------------------------------------
    > mangesh_yadav's Profile:

    http://www.excelforum.com/member.php...o&userid=10470
    > View this thread: http://www.excelforum.com/showthread...hreadid=376237
    >




  10. #10
    Mangesh Yadav
    Guest

    Re: Converting Minutes:Seconds to Minutes

    Hi Bob,

    What my formula does is convert the OP's readings into integer minutes. For
    instance, 94:00:00 is converted to 94. All the results are integers. The sum
    will be total minutes.

    If the OP further wants to convert this into actual time, then he could use
    something like:
    =TIME(0,B1,0)
    where B1 holds the result of the previous formula, or the result of the sum
    of all such numbers.
    94 would give him 1:34:00


    By the way, your formula gave me 4:07:07 for the 94:00:00 reading.


    Mangesh





    "Bob Phillips" <[email protected]> wrote in message
    news:#[email protected]...
    > That way would convert the 94:00:00 example he gave wrongly, the OP stated
    > that was 1 hour 34 minutes in reality.
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > "mangesh_yadav"

    <[email protected]>
    > wrote in message
    > news:[email protected]...
    > >
    > > Hi Nick,
    > >
    > > After getting the values into excel, the data seems to be formatted as
    > > dates, and minutes as hours. Do the following:
    > >
    > > Assuming your minutes data is in A1 and down, enter the following
    > > formula in column B and copy down:
    > > =HOUR(A1)+DAY(A1)*24
    > >
    > > And then sum them using sum
    > > =SUM(B1:B10)
    > >
    > > Your example below gave me 82 minutes by using the above method.
    > >
    > > Mangesh
    > >
    > >
    > >
    > >
    > >
    > > >
    > > > When I copy or download the bill this column appears as (Same example)
    > > > 5:00................(for 5 minutes)
    > > > 16:00.............(for 16 minutes)
    > > > 29:00:00.............(for 29 minutes)
    > > > 32:00:00.............(for 32 minutes)

    > >
    > >
    > > --
    > > mangesh_yadav
    > > ------------------------------------------------------------------------
    > > mangesh_yadav's Profile:

    > http://www.excelforum.com/member.php...o&userid=10470
    > > View this thread:

    http://www.excelforum.com/showthread...hreadid=376237
    > >

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1