locked
Excel Autofill wrong OADate values RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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?


    Monday, July 20, 2015 8:49 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    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.
    Friday, July 24, 2015 3:28 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    In A3, use

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

    or

    =A2+1/24

    or

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

    and copy that down.



    Monday, July 20, 2015 7:23 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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?

    Friday, July 24, 2015 2:33 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    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.
    Friday, July 24, 2015 3:28 PM