Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: stuck on date time addition

  1. #1
    Board Regular
    Join Date
    Apr 2010
    Posts
    9,393
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default stuck on date time addition

    tom 05:55 01/08/2017 05:55 01/08/2017 00:00:01
    sid 05:59 01/08/2017 05:59 01/08/2017 00:00:01
    ann 06:00 01/08/2017 06:00 01/08/2017 00:00:01
    jo 06:08 01/08/2017 06:08 01/08/2017 00:00:01
    tom 08:00 01/08/2017 08:00 01/08/2017 00:00:01
    tom 08:05 01/08/2017 08:05 01/08/2017 00:00:01
    sid 09:15 01/08/2017 09:15 01/08/2017 00:00:01
    ann 09:20 01/08/2017 09:20 01/08/2017 00:00:01
    sid 09:25 01/08/2017 09:25 01/08/2017 00:00:01
    ann 09:30 01/08/2017 09:30 01/08/2017 00:00:01
    tom 14:00 01/08/2017 14:00 01/08/2017 00:00:01
    sid 14:05 01/08/2017 14:05 01/08/2017 00:00:01
    ann 14:06 01/08/2017 14:06 01/08/2017 00:00:01
    jo 14:07 01/08/2017 14:07 01/08/2017 00:00:01
    tom 05:55 05/03/2135 05:55 ##### 02/08/2017 00:00:01
    sid 05:59 05/03/2135 05:59 02/08/2017 00:00:01
    ann 06:00 05/03/2135 06:00 02/08/2017 00:00:01
    jo 06:08 05/03/2135 06:08 02/08/2017 00:00:01
    tom 08:00 05/03/2135 08:00 02/08/2017 00:00:01
    tom 08:05 05/03/2135 08:05 02/08/2017 00:00:01
    sid 09:15 05/03/2135 09:15 02/08/2017 00:00:01
    ann 09:20 05/03/2135 09:20 02/08/2017 00:00:01
    sid 09:25 05/03/2135 09:25 02/08/2017 00:00:01
    ann 09:30 05/03/2135 09:30 02/08/2017 00:00:01
    tom 14:00 05/03/2135 14:00 02/08/2017 00:00:01
    sid 14:05 05/03/2135 14:05 02/08/2017 00:00:01
    ann 14:06 05/03/2135 14:06 02/08/2017 00:00:01
    jo 14:07 05/03/2135 14:07 02/08/2017 00:00:01
    C column is F column + B column
    why are rows 1 to 14 correct
    and rows 15 to 28 not ?
    formula in marked cell is
    =F15+B15

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    8,188
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: stuck on date time addition

    only thing i'm thinking is there is a maximum [h] value which exceeds the 18 year offset, though i'm not sure i understand the problem > The maximum time value that you can type into a cell is 9999:59:59. If you type a value that is greater than or equal to 10,000 hours (10000:00:00), the time appears as a text string.
    Last edited by mole999; Aug 13th, 2017 at 08:19 AM.
    • Yes I know there are better ways to do it. I just wish I knew them. - 2003, 2007, 2010, 2013 & 2016
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  3. #3
    Board Regular
    Join Date
    Apr 2010
    Posts
    9,393
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: stuck on date time addition

    I am adding 5 hours 55 minutes to 02/08/2017 00:00:01

  4. #4
    Board Regular
    Join Date
    Apr 2010
    Posts
    9,393
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: stuck on date time addition

    tom 05:55:00 01/08/2017 05:55 day1 01/08/2017 00:00:01 00:05:00
    sid 05:59:00 01/08/2017 05:59 day1 01/08/2017 00:00:01
    ann 06:00:00 01/08/2017 06:00 day1 01/08/2017 00:00:01 01/08/2017 00:00:01 day1
    jo 06:08:00 01/08/2017 06:08 day1 01/08/2017 00:00:01 02/08/2017 00:00:01 day2
    tom 08:00:00 01/08/2017 08:00 day1 01/08/2017 00:00:01 03/08/2017 00:00:01 day3
    tom 08:05:00 01/08/2017 08:05 day1 01/08/2017 00:00:01 04/08/2017 00:00:01 day4
    sid 09:15:00 01/08/2017 09:15 day1 01/08/2017 00:00:01 05/08/2017 00:00:01 day5
    ann 09:20:00 01/08/2017 09:20 day1 01/08/2017 00:00:01
    sid 09:25:00 01/08/2017 09:25 day1 01/08/2017 00:00:01
    ann 09:30:00 01/08/2017 09:30 day1 01/08/2017 00:00:01
    tom 14:00:00 01/08/2017 14:00 day1 01/08/2017 00:00:01
    sid 14:05:00 01/08/2017 14:05 day1 01/08/2017 00:00:01
    ann 14:06:00 01/08/2017 14:06 day1 01/08/2017 00:00:01
    jo 14:07:00 01/08/2017 14:07 day1 01/08/2017 00:00:01
    tom 06:00:00 02/08/2017 06:00 day2 ##### 02/08/2017 00:00:01
    sid 06:04:00 02/08/2017 06:04 day2 02/08/2017 00:00:01
    ann 06:05:00 02/08/2017 06:05 day2 02/08/2017 00:00:01
    jo 06:13:00 02/08/2017 06:13 day2 02/08/2017 00:00:01
    tom 08:05:00 02/08/2017 08:05 day2 02/08/2017 00:00:01
    tom 08:10:00 02/08/2017 08:10 day2 02/08/2017 00:00:01
    sid 09:20:00 02/08/2017 09:20 day2 02/08/2017 00:00:01
    ann 09:25:00 02/08/2017 09:25 day2 02/08/2017 00:00:01
    sid 09:30:00 02/08/2017 09:30 day2 02/08/2017 00:00:01
    ann 09:35:00 02/08/2017 09:35 day2 02/08/2017 00:00:01
    tom 14:05:00 02/08/2017 14:05 day2 02/08/2017 00:00:01
    sid 14:10:00 02/08/2017 14:10 day2 02/08/2017 00:00:01
    ann 14:11:00 02/08/2017 14:11 day2 02/08/2017 00:00:01
    jo 14:12:00 02/08/2017 14:12 day2 02/08/2017 00:00:01
    C column is F column + B column
    why are rows 1 to 14 correct
    and rows 15 to 28 not ?
    formula in marked cell is
    =F15+B15
    PROBLEM SOLVED TEMPORARILY BY COPYING
    b1:b14 TO b15:B28
    THEN b15 REDEFINED AS B1+$H$1
    where h1 is 5 minutes

  5. #5
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    8,460
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: stuck on date time addition

    I know you have found a tempory workaround but I am finding it difficult to work out what is the issue in your case because I get the correct results but if you format one of the 2/8/2017 cells in column F as a number (obviously with a copy of the data when you had the issue) what number does it return and are both columns C and F hard-typed or formulas?

    Also is the data in columns C and F downloaded/imported rather than typed or formula results?
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, Excel jeanie, RoryA addin (Win & Mac) or Borders-Copy-Paste

  6. #6
    Board Regular
    Join Date
    Apr 2010
    Posts
    9,393
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: stuck on date time addition

    Mark - I typed them in myself. The third column is just adding the date to the time to get a combined date_time. The problem is / was I think to do with the times. I retyped the marked cell to no avail but copying b1 to b15 solved it. I think best to move on.... thanks.

User Tag List

Tags for this Thread

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
  •  


DMCA.com