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?