stuck on date time addition

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,010
tom05:5501/08/2017 05:5501/08/2017 00:00:01
sid05:5901/08/2017 05:5901/08/2017 00:00:01
ann06:0001/08/2017 06:0001/08/2017 00:00:01
jo06:0801/08/2017 06:0801/08/2017 00:00:01
tom08:0001/08/2017 08:0001/08/2017 00:00:01
tom08:0501/08/2017 08:0501/08/2017 00:00:01
sid09:1501/08/2017 09:1501/08/2017 00:00:01
ann09:2001/08/2017 09:2001/08/2017 00:00:01
sid09:2501/08/2017 09:2501/08/2017 00:00:01
ann09:3001/08/2017 09:3001/08/2017 00:00:01
tom14:0001/08/2017 14:0001/08/2017 00:00:01
sid14:0501/08/2017 14:0501/08/2017 00:00:01
ann14:0601/08/2017 14:0601/08/2017 00:00:01
jo14:0701/08/2017 14:0701/08/2017 00:00:01
tom05:5505/03/2135 05:55#####02/08/2017 00:00:01
sid05:5905/03/2135 05:5902/08/2017 00:00:01
ann06:0005/03/2135 06:0002/08/2017 00:00:01
jo06:0805/03/2135 06:0802/08/2017 00:00:01
tom08:0005/03/2135 08:0002/08/2017 00:00:01
tom08:0505/03/2135 08:0502/08/2017 00:00:01
sid09:1505/03/2135 09:1502/08/2017 00:00:01
ann09:2005/03/2135 09:2002/08/2017 00:00:01
sid09:2505/03/2135 09:2502/08/2017 00:00:01
ann09:3005/03/2135 09:3002/08/2017 00:00:01
tom14:0005/03/2135 14:0002/08/2017 00:00:01
sid14:0505/03/2135 14:0502/08/2017 00:00:01
ann14:0605/03/2135 14:0602/08/2017 00:00:01
jo14:0705/03/2135 14:0702/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

<colgroup><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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:
Upvote 0
tom05:55:0001/08/2017 05:55day101/08/2017 00:00:0100:05:00
sid05:59:0001/08/2017 05:59day101/08/2017 00:00:01
ann06:00:0001/08/2017 06:00day101/08/2017 00:00:0101/08/2017 00:00:01day1
jo06:08:0001/08/2017 06:08day101/08/2017 00:00:0102/08/2017 00:00:01day2
tom08:00:0001/08/2017 08:00day101/08/2017 00:00:0103/08/2017 00:00:01day3
tom08:05:0001/08/2017 08:05day101/08/2017 00:00:0104/08/2017 00:00:01day4
sid09:15:0001/08/2017 09:15day101/08/2017 00:00:0105/08/2017 00:00:01day5
ann09:20:0001/08/2017 09:20day101/08/2017 00:00:01
sid09:25:0001/08/2017 09:25day101/08/2017 00:00:01
ann09:30:0001/08/2017 09:30day101/08/2017 00:00:01
tom14:00:0001/08/2017 14:00day101/08/2017 00:00:01
sid14:05:0001/08/2017 14:05day101/08/2017 00:00:01
ann14:06:0001/08/2017 14:06day101/08/2017 00:00:01
jo14:07:0001/08/2017 14:07day101/08/2017 00:00:01
tom06:00:0002/08/2017 06:00day2#####02/08/2017 00:00:01
sid06:04:0002/08/2017 06:04day202/08/2017 00:00:01
ann06:05:0002/08/2017 06:05day202/08/2017 00:00:01
jo06:13:0002/08/2017 06:13day202/08/2017 00:00:01
tom08:05:0002/08/2017 08:05day202/08/2017 00:00:01
tom08:10:0002/08/2017 08:10day202/08/2017 00:00:01
sid09:20:0002/08/2017 09:20day202/08/2017 00:00:01
ann09:25:0002/08/2017 09:25day202/08/2017 00:00:01
sid09:30:0002/08/2017 09:30day202/08/2017 00:00:01
ann09:35:0002/08/2017 09:35day202/08/2017 00:00:01
tom14:05:0002/08/2017 14:05day202/08/2017 00:00:01
sid14:10:0002/08/2017 14:10day202/08/2017 00:00:01
ann14:11:0002/08/2017 14:11day202/08/2017 00:00:01
jo14:12:0002/08/2017 14:12day202/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

<colgroup><col><col><col><col span="2"><col><col span="3"><col><col span="2"></colgroup><tbody>
</tbody>
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top