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,180
    Post Thanks / Like
    Mentioned
    0 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,128
    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,180
    Post Thanks / Like
    Mentioned
    0 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,180
    Post Thanks / Like
    Mentioned
    0 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,359
    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,180
    Post Thanks / Like
    Mentioned
    0 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
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com