Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Trying again for time in tenths of an hour.

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    206
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The numbers i use are in tenth of an hour, the time is for starting time (-) ending time (=) hours. or,
    ...f7.........e7...(=)...g7.
    ..5:30pm....2:00am.(=).8.5hrs.
    I need:
    ...5.5pm... 2.0am .(=).8.5 hrs.
    I have tryed formating cells to costom 0.00"hours"
    in g7,,,=if(f7-e7<0,f7-e7+1,f7-e7)*24. and it returns #VALUE, in g7. I can't seem to get this problem past the midnight hour without creating a 36 hour day.
    What am i doing wrong or can i get this thing to give me tenths for time?

    [ This Message was edited by: cblincoln43 on 2002-04-07 05:32 ]

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Please do a search. There are multiple examples on this website.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi there

    Not sure I fully understand what you mean by tenths of an hour format, but you need to use an if statement when time falls into the following day.

    From 10pm to 2am you use an if statement that says - If the first time is greater than the second time, add 12 hours to the second time before deducting the first time (add 24 hours if using military time).

    hope this helps
    regards
    Derek

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Posts
    206
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    search as ? don' mean to be to dumn as thats what ive been doing for a day or two.
    In tenth the time changes every 6 min.

    [ This Message was edited by: cblincoln43 on 2002-04-07 05:55 ]

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

    Default

    On 2002-04-07 05:31, cblincoln43 wrote:
    The numbers i use are in tenth of an hour, the time is for starting time (-) ending time (=) hours. or,
    ...f7.........e7...(=)...g7.
    ..5:30pm....2:00am.(=).8.5hrs.
    I need:
    ...5.5pm... 2.0am .(=).8.5 hrs.
    I have tryed formating cells to costom 0.00"hours"
    in g7,,,=if(f7-e7<0,f7-e7+1,f7-e7)*24. and it returns #VALUE, in g7. I can't seem to get this problem past the midnight hour without creating a 36 hour day.
    What am i doing wrong or can i get this thing to give me tenths for time?

    [ This Message was edited by: cblincoln43 on 2002-04-07 05:32 ]
    State clearly what you have in E7 and F7. Which is start time and which is the end time? The best way to do this is: Select an empty cell, type =, select 5 rows data from E and F (including labels), hit F9, copy waht you see, and paste the copied bit in the follow up. It seems: you want to compute the difference between E and F values in G where the results must be in decimal times. Right?


  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    206
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Right, as in g7,,,=sum(f7-e7) only in time that will be in tenths of an hour and go past the midnight hour.

  7. #7
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    Try something like:
    =IF(F7<0.5,F97*24,(F7-0.5)*24)

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

    Default

    On 2002-04-07 06:55, cblincoln43 wrote:
    Right, as in g7,,,=sum(f7-e7) only in time that will be in tenths of an hour and go past the midnight hour.
    In G7 enter:

    =(F7+(F7
    Format G7 as General.

    BTW, I wonder why you did not (want to) "Select an empty cell, type =, select 5 rows data from E and F (including labels), hit F9, copy waht you see, and paste the copied bit in the follow up."

    [ This Message was edited by: Aladin Akyurek on 2002-04-07 07:24 ]

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Posts
    206
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin, simply had not tryed it yet. The formula in last post? looks like what im using only ive had to creat a 36 hour day to make it work. as in 5:00pm=17.0 and 2:00am=26.0. so i have to go beyond the normal 24hour day.
    Dave, In your post did you mean =if(f7<0.5,f7*24,f7-0.5)*24 or =if(f7<0.5,f9-f7*24,f9-0.5)*24 ??

    [ This Message was edited by: cblincoln43 on 2002-04-07 07:28 ]

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

    Default

    On 2002-04-07 07:22, cblincoln43 wrote:
    Aladin, simply had not tryed it yet. The formula in last post? looks like what im using only ive had to creat a 36 hour day to make it work. as in 5:00pm=17.0 and 2:00am=26.0. so i have to go beyond the normal 24hour day.
    Try the formula I posted (Now, you should see the whole formula, uncrippled by HTML).

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
  •