Thanks:  0
Likes:  0

1. ## 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. ## 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.

3. ## Re: stuck on date time addition

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

4. ## 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. ## 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?

6. ## 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

#### Posting Permissions

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