I'm currently developing an application that's importing data from Excel to a database using Open XML SDK.The problem is a field that holds a DateTime value in a custom number format d.m.yyyy h:mm.

I noticed that when using Autofill (select two cells & drag) Excel generates wrong OADate values. If a DateTime value is input manually the OADate value is correct.

DateTime              Expected value                 Excel                                        Diff
14.7.2015 0:00      42199,0000000000    42199,000000000000    0,000000000000
14.7.2015 1:00      42199,0416666667    42199,041666666600    0,000000000102
14.7.2015 2:00      42199,0833333333    42199,083333217500    0,000000115797
14.7.2015 3:00      42199,1250000000    42199,124999826300    0,000000173699
14.7.2015 4:00      42199,1666666667    42199,166666435100    0,000000231601
14.7.2015 5:00      42199,2083333333    42199,208333043900    0,000000289401
14.7.2015 6:00      42199,2500000000    42199,249999652700    0,000000347303
14.7.2015 7:00      42199,2916666667    42199,291666261500    0,000000405198
14.7.2015 8:00      42199,3333333333    42199,333332870300    0,000000462998
14.7.2015 9:00      42199,3750000000    42199,374999479100    0,000000520900
14.7.2015 10:00    42199,4166666667    42199,416666087900    0,000000578802
14.7.2015 11:00    42199,4583333333    42199,458332696600    0,000000636603
14.7.2015 12:00    42199,5000000000    42199,499999305500    0,000000694497
14.7.2015 13:00    42199,5416666667    42199,541665914300    0,000000752400
14.7.2015 14:00    42199,5833333333    42199,583332523100    0,000000810200
14.7.2015 15:00    42199,6250000000    42199,624999131900    0,000000868102
14.7.2015 16:00    42199,6666666667    42199,666665740700    0,000000926004
14.7.2015 17:00    42199,7083333333    42199,708332349500    0,000000983797
14.7.2015 18:00    42199,7500000000    42199,749998958300    0,000001041699
14.7.2015 19:00    42199,7916666667    42199,791665567100    0,000001099601
14.7.2015 20:00    42199,8333333333    42199,833332175900    0,000001157401
14.7.2015 21:00    42199,8750000000    42199,874998784700    0,000001215303
14.7.2015 22:00    42199,9166666667    42199,916665393500    0,000001273198
14.7.2015 23:00    42199,9583333333    42199,958333333300    0,000000000000

As you can see the autofill values are more off with every subsequent autofill value.

If a convert this values in a .NET application (.ToOADate()) I get wrong DateTime values, however Excel still displays them right. Can anyone else confirm this?

In A3, use

=A2+TIMEVALUE("1:00:00")

or

=A2+1/24

or

=$A$2 + ROW(A1)/24

and copy that down.

• Edited by Bernie Deitrick, Excel MVP 2000-2010Microsoft community contributor 12 hours 5 minutes ago

In A3, use

=A2+TIMEVALUE("1:00:00")

or

=A2+1/24

or

=$A$2 + ROW(A1)/24

and copy that down.

• Edited by Bernie Deitrick, Excel MVP 2000-2010Microsoft community contributor Monday, July 20, 2015 7:26 PM

I'm aware that it works correctly with a formula.

But the import files are user generated and if they decide to use autofill (without a formula) the dates are still off.

I'm just curious, is this a known limitation or a bug?

The autofill is based on the difference between the first two cells, which is just added repeatedly, so it all depends on how the original values are generated.