0

This has to be easy, and extremely common, but I can't find the answer with Google.

I have a column with years (number format) in Excel. I need that column in Excel to be in date format for importing into Tableau. (There may be a way to fix this in Tableua - not relevant here.)

I see way to have a date only show the year in Excel ('yyy').

When I convert my number to a date, I get this:
1999 -> format cells (*3/4/2012) -> 06/21/1905
06/21/1905 -> format cells (yyyy) -> 1905
1999 -> format cells (yyyy) -> 1905

I want 1999 (convert to date format) -> 1999

Thanks for any help.

4
  • 1
    What day in 1999? You can't just convert 1999 to a date in 1999 without additional information in Excel – Ramhound Mar 18 at 16:26
  • if you format 1999 to date time it'll mean the 1999th day since the epoch (00-Jan-1900 in Excel). Try ([datetime]"1899-12-31").AddDays(1998) in PowerShell and see how it prints exactly that date. You can't mix year-only and datetime data withing a single date format – phuclv Mar 18 at 16:42
  • 1999 isn't a date; it's part of a date. You have to pick a month and day, too. If Jan 1 is OK, then you can use this formula: =DATE(A1,1,1). That will return a date value of Jan 1 in the year given as a number in A1. – Engineer Toast Mar 18 at 17:41
  • @EngineerToast Your comment works. The "1999" is not obliterated. That is a formula I can copy to cells to replace the date column with. It's better than the answer to my own question I posted - where I just manually typed in 1/1/x in all the rows. Post it as an answer and I'll accept it. – Dave Mar 18 at 20:22
1

1999 isn't a date; it's part of a date. You have to pick a month and day, too. If Jan 1 is OK, then you can use this formula: =DATE(A1,1,1). That will return a date value of Jan 1 in the year given as a number in A1. You can also copy/paste this as needed so you have both the original data and the date value for an entire range of cells.

1
  • This eliminates the problem where coverting 1999 to a date clobbers 1999 - and it's a formula I can copy across cells, rather than being a macro. Thanks. – Dave Mar 18 at 22:00
0
  1. type 1999-01-01 in e.g. cell A1, this will normally also format it as a date
  2. type =A1 in A2 and set formatting to Number (using the dialog launched by CTRL-1) - this will tell the number that represents Jan 1 1999.
  3. type a 0 in B1, set the format to Date or Time showing Date - it will tell what date a zero value represents. The number in A2 tells how many days A1 is from the base date (in B1) which is encoded as zero.

Assuming that B1 shows "1900" (or "1899-12-30 00:00", LibreOffice)...
Type 1999 in C1, type =(C1-1900)*365.263 in C2. Format C2 as Date

2
  • I am aware that I can type 1/1/1999, 1/1/2000 and manually replace every year. It's a lot easier than you describe. You just type in the full date, and then change the format to 'yyyy.' ---- I'm looking for a way to do this without manually typing in all the data. – Dave Mar 18 at 17:31
  • ... hence the formula; as an indication of how to do it. =DATE(y,m,d) is of course a better way. – Hannu Mar 19 at 19:55
-1

I just went through and did this manually.

I made a new column, set its format to date, and typed 1/1/1999 for the first row, 1/1/2000 for the second, etc.

Then I changed the format of the column to 'yyyy' so it just shows the year.

Now Tableau is working as expected with this Excel sheet. (It does a "sum" on the year field, and there's only one data point per year, so the sum is the value I want in the line).

I will check back later to see if someone posted an answer that doesn't require the person to manually type in every date. There probably a way to do this with a macro, but that's dirty IMO.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.