Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Formatting time/decimal

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Inverness, Scotland
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a similar problem. I am a night shift worker, and my employer issues a time statement each week in the format of start time, end time, total. The problem is that in order to reproduce this in Excel, I must calculate accordingly. My result looks like this: 18:45, 08:30, 13:45. My time statement reads thus: 18:45, 08:30, 13.75. For the life of me, I can't get the calculation, (formula?) to same result. Help would be appreciated.

    Thanks in advance

    Jake

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-02-17 03:56, Jakeyboy wrote:
    I have a similar problem. I am a night shift worker, and my employer issues a time statement each week in the format of start time, end time, total. The problem is that in order to reproduce this in Excel, I must calculate accordingly. My result looks like this: 18:45, 08:30, 13:45. My time statement reads thus: 18:45, 08:30, 13.75. For the life of me, I can't get the calculation, (formula?) to same result. Help would be appreciated.

    Thanks in advance

    Jake
    The formula that you can use is:

    =(B1+(B1
    where A1 houses the start time and B1 the end time.

    Format the cell of this formula as General.

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    Inverness, Scotland
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Many thanks Aladin, but this formula doesn't work I'm afraid.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-02-17 04:24, Aladin Akyurek wrote:
    On 2002-02-17 03:56, Jakeyboy wrote:
    I have a similar problem. I am a night shift worker, and my employer issues a time statement each week in the format of start time, end time, total. The problem is that in order to reproduce this in Excel, I must calculate accordingly. My result looks like this: 18:45, 08:30, 13:45. My time statement reads thus: 18:45, 08:30, 13.75. For the life of me, I can't get the calculation, (formula?) to same result. Help would be appreciated.

    Thanks in advance

    Jake
    The formula that you can use is:

    =(B1+(B1 < A1)-A1)*24

    where A1 houses the start time and B1 the end time.

    Format the cell of this formula as General.

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-02-17 06:49, Jakeyboy wrote:
    Many thanks Aladin, but this formula doesn't work I'm afraid.
    Jake: The formula was crippled, because of the existence of the less than sign. I thought the new board would not have this symptom of the old board. It appears we have still to watch out for it. See the one that is fixed.

  6. #6
    New Member
    Join Date
    Feb 2002
    Location
    Inverness, Scotland
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin,

    Many thanks, the repaired version has worked perfectly. I know who to ask if I've any more problems

    Thanks again

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Leicestershire, U K
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This reply in in sync with ALadin AKyurek question on Overtime

    Further to my previous answer you can change column f to this and this will return zero if there is no start and finish times.

    If you change the cell F1 to
    =IF(AND(A1=0,B1=0,TRUE),0,IF(C!=0,24,D1*24))
    This will test for nil entry in both start and finish times

    This is because in our business we work 365 days and you could have the same start and finish time which would mean a day has expired
    Hope this helps
    kk

  8. #8
    Guest

    Default

    HOw bout this problem.
    1 hour = $25
    30 minutes = $15
    less than 30mins = $10

    Time In TimeOut Total Time Charge
    6:30 pm 7:00 pm 30mins $15
    7:30 pm 9:00 pm 1hour30mins $40

    how do i create the formula from Total Time and Charge... Is it possible that the formula automatically calculates the charge time taking in consideration that for a half hour its just 15$? and that usage for less than 30minutes would be $10?

    I really appreciate the help here... thanks

  9. #9
    New Member
    Join Date
    Feb 2002
    Location
    Space
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The anonymous post was my post... need the help a lot.. thanks

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    On 2002-02-18 07:31, Anonymous wrote:
    HOw bout this problem.
    1 hour = $25
    30 minutes = $15
    less than 30mins = $10

    Time In TimeOut Total Time Charge
    6:30 pm 7:00 pm 30mins $15
    7:30 pm 9:00 pm 1hour30mins $40

    how do i create the formula from Total Time and Charge... Is it possible that the formula automatically calculates the charge time taking in consideration that for a half hour its just 15$? and that usage for less than 30minutes would be $10?

    I really appreciate the help here... thanks
    Given a total time in the format 2:45, 7:40, etc., it seems you're looking for:

    =HOUR(E1)*25+(MINUTE(E1)>=30)*15+(MINUTE(E1)>30)*10+(MINUTE(E1)<30)*10

    where E1 holds a total time.

    Perhaps I misunderstood, but the payment scheme looks a bit irrational to me.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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