Dates won't format as dates when converted as headers in table

  • hi there,


    I'm losing my mind here.

    I had a normal range I converted to an Excel table with the headers. Most of the headers are dates.

    No matter what I try, I cannot seem to convert them to read as dates!

    I'm not sure why. I need them as dates as they are used in formulas.


    I've attached the table (with the data cleared). I just need the headers from K onwards to read as dates.

    Can someone please tell me what I'm doing wrong? I've been googling for a while now and gave up. :(


    Thank you!

    Files

    • Sample.xlsx

      (21.8 kB, downloaded 122 times, last: )
  • Hi,


    Could you attach the original ' standard ' range ... before converting it into a Table ...


    To me ... you do have actual dates in your Table ...


    For example cell K1 is 2nd of January 2018 ....

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Here are the headers for the original file - these ones work as normal range.

    They are reading as dates.

    When I converted it to a table, the dates read weird. You said January 2, 2018. It's actually supposed to be January 1, 2018.

    For some reason, I think it's reading the headers as text, not dates, even though it looks like dates. No matter what I did, it wouldn't convert.

    I need to do date comparisons with it but it's not calculating properly because of that :(.


    Thanks in advance, Carim.

    Files

    • Sample2.xlsx

      (11.25 kB, downloaded 122 times, last: )
  • Hi again,


    With your Sample 2 file ... at my end ...converting your range into a Table ... and also back to standard range ... does always produce Headers as Dates ... :huh:


    Which Excel version do you use ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Would have to check if there is an issue with Excel 2010 ... with Excel 2016, everything is fine ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Conversion works fine with Office 365 too

    Files

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Sorry cannot help you any further ...


    But apparently ... there is a weird table header row formatting with Excel 2010 ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Try


    =IF(DATEVALUE(Table9[[#Headers],[Jan-18]])>TODAY(),"YES","NO")

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • That worked!


    The actual formula actually looks like this:

    =IF(AND(N$2>=$F4,N$2<$G4),$J4,IF(AND(N$2>=$G4,N$2<$H4),$K4,IF(AND(N$2>=$H4,N$2<$I4),$L4,IF(N$2=$I4,$M4,0))))


    where in N$2 is where the Date header is.


    So I have to put DateValue everytime I reference the header field?

  • You can't change the header format to actual dates..


    Try changing formula to adapt:


    =IF((REPLACE(Table9[[#Headers],[Jan-18]],4,1," 1, "))+0>TODAY(),"YES","NO")

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Yes, by default headers in an Excel built-in table are text so dates need to have the date value of the date text when used in a formula, you should only need to change one formula, then copy across

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • For me the DateValue() function gave a date of Jan 18/19. Is that what you want?

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Try my formula in post #14:



    Code
    1. =IF((REPLACE(Table9[[#Headers],[Jan-18]],4,1," 1, "))+0>TODAY(),"YES","NO")

    Where there is a will there are many ways. Finding one that works for you is the challenge!