+ Reply to Thread
Results 1 to 19 of 19

Calculate time difference to the half hour

  1. #1
    Ken Ivins
    Guest

    Calculate time difference to the half hour

    I am creating an employee time sheet that the put in their starting time and
    ending time (From a drop down list). I would like Excel to calculate the
    amount of time showing full and if need be a half hour. My problem is that
    the results half only been in full hours. Such as 9 am to 5 pm comes out as
    8 hours. But also 9 am to 4:30 pm also comes out as 7 hours instead of 7.5
    hours.

    I have tried three types of formulas with the same result:

    =TEXT(F11-E11,"h")

    =(INT(F10-E10*24))

    =hour(f10-E10)

    Any ideas on getting the results I need?

    Thanks,
    Ken



  2. #2
    Don Guillett
    Guest

    Re: Calculate time difference to the half hour

    try this
    =ROUND(A1/TIME(0,30,0),0)*TIME(0,30,0)

    --
    Don Guillett
    SalesAid Software
    donaldb@281.com
    "Ken Ivins" <ken-express@kvins.com> wrote in message
    news:utCnc1vAFHA.1452@TK2MSFTNGP11.phx.gbl...
    > I am creating an employee time sheet that the put in their starting time

    and
    > ending time (From a drop down list). I would like Excel to calculate the
    > amount of time showing full and if need be a half hour. My problem is that
    > the results half only been in full hours. Such as 9 am to 5 pm comes out

    as
    > 8 hours. But also 9 am to 4:30 pm also comes out as 7 hours instead of 7.5
    > hours.
    >
    > I have tried three types of formulas with the same result:
    >
    > =TEXT(F11-E11,"h")
    >
    > =(INT(F10-E10*24))
    >
    > =hour(f10-E10)
    >
    > Any ideas on getting the results I need?
    >
    > Thanks,
    > Ken
    >
    >




  3. #3
    Ken Ivins
    Guest

    Re: Calculate time difference to the half hour

    Don,

    Thanks for your help. I tried this a few different ways with out getting
    the results I'm looking for. Any other ideas?

    Ken




    "Don Guillett" <donaldb@281.com> wrote in message
    news:eTalG%23vAFHA.608@TK2MSFTNGP15.phx.gbl...
    > try this
    > =ROUND(A1/TIME(0,30,0),0)*TIME(0,30,0)
    >
    > --
    > Don Guillett
    > SalesAid Software
    > donaldb@281.com
    > "Ken Ivins" <ken-express@kvins.com> wrote in message
    > news:utCnc1vAFHA.1452@TK2MSFTNGP11.phx.gbl...
    >> I am creating an employee time sheet that the put in their starting time

    > and
    >> ending time (From a drop down list). I would like Excel to calculate the
    >> amount of time showing full and if need be a half hour. My problem is
    >> that
    >> the results half only been in full hours. Such as 9 am to 5 pm comes out

    > as
    >> 8 hours. But also 9 am to 4:30 pm also comes out as 7 hours instead of
    >> 7.5
    >> hours.
    >>
    >> I have tried three types of formulas with the same result:
    >>
    >> =TEXT(F11-E11,"h")
    >>
    >> =(INT(F10-E10*24))
    >>
    >> =hour(f10-E10)
    >>
    >> Any ideas on getting the results I need?
    >>
    >> Thanks,
    >> Ken
    >>
    >>

    >
    >




  4. #4
    Ken Ivins
    Guest

    Re: Calculate time difference to the half hour

    Okay, This seemed to work.

    =((TEXT(F10-E10,"h:mm"))-INT(TEXT(F10-E10,"h:mm")))*24

    Anyone see a problem with this?

    Thanks,
    Ken



    "Ken Ivins" <ken-express@kvins.com> wrote in message
    news:uKujENwAFHA.2016@TK2MSFTNGP15.phx.gbl...
    > Don,
    >
    > Thanks for your help. I tried this a few different ways with out
    > getting the results I'm looking for. Any other ideas?
    >
    > Ken
    >
    >
    >
    >
    > "Don Guillett" <donaldb@281.com> wrote in message
    > news:eTalG%23vAFHA.608@TK2MSFTNGP15.phx.gbl...
    >> try this
    >> =ROUND(A1/TIME(0,30,0),0)*TIME(0,30,0)
    >>
    >> --
    >> Don Guillett
    >> SalesAid Software
    >> donaldb@281.com
    >> "Ken Ivins" <ken-express@kvins.com> wrote in message
    >> news:utCnc1vAFHA.1452@TK2MSFTNGP11.phx.gbl...
    >>> I am creating an employee time sheet that the put in their starting time

    >> and
    >>> ending time (From a drop down list). I would like Excel to calculate the
    >>> amount of time showing full and if need be a half hour. My problem is
    >>> that
    >>> the results half only been in full hours. Such as 9 am to 5 pm comes out

    >> as
    >>> 8 hours. But also 9 am to 4:30 pm also comes out as 7 hours instead of
    >>> 7.5
    >>> hours.
    >>>
    >>> I have tried three types of formulas with the same result:
    >>>
    >>> =TEXT(F11-E11,"h")
    >>>
    >>> =(INT(F10-E10*24))
    >>>
    >>> =hour(f10-E10)
    >>>
    >>> Any ideas on getting the results I need?
    >>>
    >>> Thanks,
    >>> Ken
    >>>
    >>>

    >>
    >>

    >
    >




  5. #5
    Tim C
    Guest

    Re: Calculate time difference to the half hour

    Ken,

    It's unclear what format you want the result to be in.

    Try:

    =INT((F10-E10)*48)/2

    and format as a number, or

    =INT((F10-E10)*48)/48

    and format as time.

    Tim C

    "Ken Ivins" <ken-express@kvins.com> wrote in message
    news:u2vueZwAFHA.2788@TK2MSFTNGP15.phx.gbl...
    > Okay, This seemed to work.
    >
    > =((TEXT(F10-E10,"h:mm"))-INT(TEXT(F10-E10,"h:mm")))*24
    >
    > Anyone see a problem with this?
    >
    > Thanks,
    > Ken
    >
    >
    >
    > "Ken Ivins" <ken-express@kvins.com> wrote in message
    > news:uKujENwAFHA.2016@TK2MSFTNGP15.phx.gbl...
    >> Don,
    >>
    >> Thanks for your help. I tried this a few different ways with out
    >> getting the results I'm looking for. Any other ideas?
    >>
    >> Ken
    >>
    >>
    >>
    >>
    >> "Don Guillett" <donaldb@281.com> wrote in message
    >> news:eTalG%23vAFHA.608@TK2MSFTNGP15.phx.gbl...
    >>> try this
    >>> =ROUND(A1/TIME(0,30,0),0)*TIME(0,30,0)
    >>>
    >>> --
    >>> Don Guillett
    >>> SalesAid Software
    >>> donaldb@281.com
    >>> "Ken Ivins" <ken-express@kvins.com> wrote in message
    >>> news:utCnc1vAFHA.1452@TK2MSFTNGP11.phx.gbl...
    >>>> I am creating an employee time sheet that the put in their starting
    >>>> time
    >>> and
    >>>> ending time (From a drop down list). I would like Excel to calculate
    >>>> the
    >>>> amount of time showing full and if need be a half hour. My problem is
    >>>> that
    >>>> the results half only been in full hours. Such as 9 am to 5 pm comes
    >>>> out
    >>> as
    >>>> 8 hours. But also 9 am to 4:30 pm also comes out as 7 hours instead of
    >>>> 7.5
    >>>> hours.
    >>>>
    >>>> I have tried three types of formulas with the same result:
    >>>>
    >>>> =TEXT(F11-E11,"h")
    >>>>
    >>>> =(INT(F10-E10*24))
    >>>>
    >>>> =hour(f10-E10)
    >>>>
    >>>> Any ideas on getting the results I need?
    >>>>
    >>>> Thanks,
    >>>> Ken
    >>>>
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >




  6. #6
    Ron Rosenfeld
    Guest

    Re: Calculate time difference to the half hour

    On Tue, 25 Jan 2005 11:46:09 -0500, "Ken Ivins" <ken-express@kvins.com> wrote:

    >I am creating an employee time sheet that the put in their starting time and
    >ending time (From a drop down list). I would like Excel to calculate the
    >amount of time showing full and if need be a half hour. My problem is that
    >the results half only been in full hours. Such as 9 am to 5 pm comes out as
    >8 hours. But also 9 am to 4:30 pm also comes out as 7 hours instead of 7.5
    >hours.
    >
    >I have tried three types of formulas with the same result:
    >
    > =TEXT(F11-E11,"h")
    >
    > =(INT(F10-E10*24))
    >
    > =hour(f10-E10)
    >
    >Any ideas on getting the results I need?
    >
    >Thanks,
    >Ken
    >


    Assumption is that your times are entered as Excel times; eg. 9 AM; 4:30 PM;
    etc.

    If you wish to round to the nearest 30 minutes, then:

    =ROUND((EndTime-StartTime)/TIME(0,30,0),0)*TIME(0,30,0)

    If you wish to round up to the next 30 minutes, so that 7:01 --> 7:30, then

    =CEILING(EndTime-StartTime,TIME(0,30,0))

    and format as [h]:mm.

    If you wish to convert either of the above to decimal numbers, then multiply
    the result by 24:

    =24 * ROUND((EndTime-StartTime)/TIME(0,30,0),0)*TIME(0,30,0)

    or

    =24 * CEILING(EndTime-StartTime,TIME(0,30,0))


    --ron

  7. #7
    infoman
    Guest

    Re: Calculate time difference to the half hour

    > Ken Ivinswrote:

    >
    > Okay, This seemed to work.
    >
    > Anyone see a problem with this?
    >
    > Thanks,
    > Ken
    >
    >



    Yes. Ask 1 question. 2 questions. Ask 10 questions. But you need so
    much help that you set up email accounts for each forum where you
    post ask questions so that you can keep it all straight.

    You run a business http://www.kivins.com charging people for the free
    help you get here. How about paying us? Why don't you learn it
    yourself the way we had to? I went to school, put in time, paid money
    to learn.

    We all give help to each other, but you just take. From us, from your
    customers.


  8. #8
    infoman
    Guest

    Re: Calculate time difference to the half hour

    > Ken Ivinswrote:

    >
    > Okay, This seemed to work.
    >
    > Anyone see a problem with this?
    >
    > Thanks,
    > Ken
    >
    >



    Yes. Ask 1 question. 2 questions. Ask 10 questions. But you need so
    much help that you set up email accounts for each forum where you
    post ask questions so that you can keep it all straight.

    You run a business http://www.kivins.com charging people for the free
    help you get here. How about paying us? Why don't you learn it
    yourself the way we had to? I went to school, put in time, paid money
    to learn.

    We all give help to each other, but you just take. From us, from your
    customers.


  9. #9
    infoman
    Guest

    Re: Calculate time difference to the half hour

    > Ken Ivinswrote:

    >
    > Okay, This seemed to work.
    >
    > Anyone see a problem with this?
    >
    > Thanks,
    > Ken
    >
    >



    Yes. Ask 1 question. 2 questions. Ask 10 questions. But you need so
    much help that you set up email accounts for each forum where you
    post ask questions so that you can keep it all straight.

    You run a business http://www.kivins.com charging people for the free
    help you get here. How about paying us? Why don't you learn it
    yourself the way we had to? I went to school, put in time, paid money
    to learn.

    We all give help to each other, but you just take. From us, from your
    customers.


  10. #10
    infoman
    Guest

    Re: Calculate time difference to the half hour

    > Ken Ivinswrote:

    >
    > Okay, This seemed to work.
    >
    > Anyone see a problem with this?
    >
    > Thanks,
    > Ken
    >
    >



    Yes. Ask 1 question. 2 questions. Ask 10 questions. But you need so
    much help that you set up email accounts for each forum where you
    post ask questions so that you can keep it all straight.

    You run a business http://www.kivins.com charging people for the free
    help you get here. How about paying us? Why don't you learn it
    yourself the way we had to? I went to school, put in time, paid money
    to learn.

    We all give help to each other, but you just take. From us, from your
    customers.


  11. #11
    infoman
    Guest

    Re: Calculate time difference to the half hour

    > Ken Ivinswrote:

    >
    > Okay, This seemed to work.
    >
    > Anyone see a problem with this?
    >
    > Thanks,
    > Ken
    >
    >



    Yes. Ask 1 question. 2 questions. Ask 10 questions. But you need so
    much help that you set up email accounts for each forum where you
    post ask questions so that you can keep it all straight.

    You run a business http://www.kivins.com charging people for the free
    help you get here. How about paying us? Why don't you learn it
    yourself the way we had to? I went to school, put in time, paid money
    to learn.

    We all give help to each other, but you just take. From us, from your
    customers.


  12. #12
    infoman
    Guest

    Re: Calculate time difference to the half hour

    > Ken Ivinswrote:

    >
    > Okay, This seemed to work.
    >
    > Anyone see a problem with this?
    >
    > Thanks,
    > Ken
    >
    >



    Yes. Ask 1 question. 2 questions. Ask 10 questions. But you need so
    much help that you set up email accounts for each forum where you
    post ask questions so that you can keep it all straight.

    You run a business http://www.kivins.com charging people for the free
    help you get here. How about paying us? Why don't you learn it
    yourself the way we had to? I went to school, put in time, paid money
    to learn.

    We all give help to each other, but you just take. From us, from your
    customers.


  13. #13
    infoman
    Guest

    Re: Calculate time difference to the half hour

    > Ken Ivinswrote:

    >
    > Okay, This seemed to work.
    >
    > Anyone see a problem with this?
    >
    > Thanks,
    > Ken
    >
    >



    Yes. Ask 1 question. 2 questions. Ask 10 questions. But you need so
    much help that you set up email accounts for each forum where you
    post ask questions so that you can keep it all straight.

    You run a business http://www.kivins.com charging people for the free
    help you get here. How about paying us? Why don't you learn it
    yourself the way we had to? I went to school, put in time, paid money
    to learn.

    We all give help to each other, but you just take. From us, from your
    customers.


  14. #14
    infoman
    Guest

    Re: Calculate time difference to the half hour

    > Ken Ivinswrote:

    >
    > Okay, This seemed to work.
    >
    > Anyone see a problem with this?
    >
    > Thanks,
    > Ken
    >
    >



    Yes. Ask 1 question. 2 questions. Ask 10 questions. But you need so
    much help that you set up email accounts for each forum where you
    post ask questions so that you can keep it all straight.

    You run a business http://www.kivins.com charging people for the free
    help you get here. How about paying us? Why don't you learn it
    yourself the way we had to? I went to school, put in time, paid money
    to learn.

    We all give help to each other, but you just take. From us, from your
    customers.


  15. #15
    infoman
    Guest

    Re: Calculate time difference to the half hour

    > Ken Ivinswrote:

    >
    > Okay, This seemed to work.
    >
    > Anyone see a problem with this?
    >
    > Thanks,
    > Ken
    >
    >



    Yes. Ask 1 question. 2 questions. Ask 10 questions. But you need so
    much help that you set up email accounts for each forum where you
    post ask questions so that you can keep it all straight.

    You run a business http://www.kivins.com charging people for the free
    help you get here. How about paying us? Why don't you learn it
    yourself the way we had to? I went to school, put in time, paid money
    to learn.

    We all give help to each other, but you just take. From us, from your
    customers.


  16. #16
    infoman
    Guest

    Re: Calculate time difference to the half hour

    > Ken Ivinswrote:

    >
    > Okay, This seemed to work.
    >
    > Anyone see a problem with this?
    >
    > Thanks,
    > Ken
    >
    >



    Yes. Ask 1 question. 2 questions. Ask 10 questions. But you need so
    much help that you set up email accounts for each forum where you
    post ask questions so that you can keep it all straight.

    You run a business http://www.kivins.com charging people for the free
    help you get here. How about paying us? Why don't you learn it
    yourself the way we had to? I went to school, put in time, paid money
    to learn.

    We all give help to each other, but you just take. From us, from your
    customers.


  17. #17
    infoman
    Guest

    Re: Calculate time difference to the half hour

    > Ken Ivinswrote:

    >
    > Okay, This seemed to work.
    >
    > Anyone see a problem with this?
    >
    > Thanks,
    > Ken
    >
    >



    Yes. Ask 1 question. 2 questions. Ask 10 questions. But you need so
    much help that you set up email accounts for each forum where you
    post ask questions so that you can keep it all straight.

    You run a business http://www.kivins.com charging people for the free
    help you get here. How about paying us? Why don't you learn it
    yourself the way we had to? I went to school, put in time, paid money
    to learn.

    We all give help to each other, but you just take. From us, from your
    customers.


  18. #18
    infoman
    Guest

    Re: Calculate time difference to the half hour

    > Ken Ivinswrote:

    >
    > Okay, This seemed to work.
    >
    > Anyone see a problem with this?
    >
    > Thanks,
    > Ken
    >
    >



    Yes. Ask 1 question. 2 questions. Ask 10 questions. But you need so
    much help that you set up email accounts for each forum where you
    post ask questions so that you can keep it all straight.

    You run a business http://www.kivins.com charging people for the free
    help you get here. How about paying us? Why don't you learn it
    yourself the way we had to? I went to school, put in time, paid money
    to learn.

    We all give help to each other, but you just take. From us, from your
    customers.


  19. #19
    infoman
    Guest

    Re: Calculate time difference to the half hour

    > Ken Ivinswrote:

    >
    > Okay, This seemed to work.
    >
    > Anyone see a problem with this?
    >
    > Thanks,
    > Ken
    >
    >



    Yes. Ask 1 question. 2 questions. Ask 10 questions. But you need so
    much help that you set up email accounts for each forum where you
    post ask questions so that you can keep it all straight.

    You run a business http://www.kivins.com charging people for the free
    help you get here. How about paying us? Why don't you learn it
    yourself the way we had to? I went to school, put in time, paid money
    to learn.

    We all give help to each other, but you just take. From us, from your
    customers.


+ 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