# Excel: Date formatting **URL:** \ **Category:** Factual Questions\ **Created:** [July 7, 2011, 10:02pm UTC](https://boards.straightdope.com/t/excel-date-formatting/588235 "2011-07-07T22:02:13Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [July 7, 2011, 10:02pm UTC](https://boards.straightdope.com/t/excel-date-formatting/588235/1 "2011-07-07T22:02:13Z")
I have a file in which the date column is three or four digits (MMYY). e.g., 411 or 1210. I want to make all dates the first of the month (since I don’t know the day) and format them as MM/01/YY. But I can’t seem to find a combination of moves that will let me do this. For now, the solution is to copy the dates into Notebook, manually put a slash between the month and the year, change the slashes to /01/, and copy the results back into Excel. (The other solution is to sort and do several F&Rs. This is a pain because changing all of the elevens to /01/11 might result in ‘11/01/11/01/11’, for example.) ---
**Author:** ![Giles](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/giles/32/60_2.png) [@Giles](https://boards.straightdope.com/u/Giles)\ **Post date:** [July 7, 2011, 10:14pm UTC](https://boards.straightdope.com/t/excel-date-formatting/588235/2 "2011-07-07T22:14:15Z")
If the date in is cell A1, then: =LEFT(a1,LEN(a1)-2)&"/01/"&RIGHT(a1,2) gets you a text string that looks like what you want. ---
**Author:** ![KneadToKnow](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kneadtoknow/32/3999_2.png) [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)\ **Post date:** [July 7, 2011, 10:16pm UTC](https://boards.straightdope.com/t/excel-date-formatting/588235/3 "2011-07-07T22:16:18Z")
Assuming your dates are in column A, add a new column B and copy this formula down: =LEFT(A1,(LEN(A1)-2))&"/01/"&RIGHT(A1,2) ETA: Great minds think alike. 🙂 ---
**Author:** ![Bag\_of\_Mostly\_Water](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bag_of_mostly_water/32/1029_2.png) [@Bag\_of\_Mostly\_Water](https://boards.straightdope.com/u/Bag_of_Mostly_Water)\ **Post date:** [July 7, 2011, 10:20pm UTC](https://boards.straightdope.com/t/excel-date-formatting/588235/4 "2011-07-07T22:20:49Z")
Use the following formula, replacing “A1” with whatever cell contains the four digit date: =DATE(A1-100\*TRUNC(A1/100),TRUNC(A1/100),1) ---
**Author:** ![KneadToKnow](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kneadtoknow/32/3999_2.png) [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)\ **Post date:** [July 7, 2011, 10:22pm UTC](https://boards.straightdope.com/t/excel-date-formatting/588235/5 "2011-07-07T22:22:09Z")
> [@Ex\_Chemist](#): > > Use the following formula, replacing “A1” with whatever cell contains the four digit date: > =DATE(A1-100\*TRUNC(A1/100),TRUNC(A1/100),1) Maybe it’s just how my PC is set up, but when I do that, I get dates in the early Twentieth Century. ---
**Author:** ![penultima\_thule](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/penultima_thule/32/3833_2.png) [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)\ **Post date:** [July 7, 2011, 10:28pm UTC](https://boards.straightdope.com/t/excel-date-formatting/588235/6 "2011-07-07T22:28:05Z")
Standing on the shoulders of giants! Add the extra formula to get an actual, sortable Excel date =value(LEFT(A1,(LEN(A1)-2))&"/01/"&RIGHT(A1,2)) This formula assumes that it’s YY, format. If you enter a Dec 2009 data as 129 it all goes pear shaped. ---
**Author:** ![beowulff](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/beowulff/32/542_2.png) [@beowulff](https://boards.straightdope.com/u/beowulff)\ **Post date:** [July 7, 2011, 10:28pm UTC](https://boards.straightdope.com/t/excel-date-formatting/588235/7 "2011-07-07T22:28:37Z")
Assuming the date is in cell D4, this works for 3 or 4 digit dates: =IF(LEN(D4)=4,CONCATENATE(“01/”,MID(D4,2,2),"/",RIGHT(D4,2)),CONCATENATE(“01/0”,MID(D4,1,1),"/",RIGHT(D4,2))) ---
**Author:** ![Bag\_of\_Mostly\_Water](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bag_of_mostly_water/32/1029_2.png) [@Bag\_of\_Mostly\_Water](https://boards.straightdope.com/u/Bag_of_Mostly_Water)\ **Post date:** [July 7, 2011, 10:34pm UTC](https://boards.straightdope.com/t/excel-date-formatting/588235/8 "2011-07-07T22:34:18Z")
> [@KneadToKnow](#): > > Maybe it’s just how my PC is set up, but when I do that, I get dates in the early Twentieth Century. This formula works for me. Probably something about different versions of Excel. Assuming the dates are all post-2000, this formula should work: =DATE(2000+A1-100\*TRUNC(A1/100),TRUNC(A1/100),1) ---
**Author:** ![penultima\_thule](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/penultima_thule/32/3833_2.png) [@penultima\_thule](https://boards.straightdope.com/u/penultima_thule)\ **Post date:** [July 7, 2011, 10:37pm UTC](https://boards.straightdope.com/t/excel-date-formatting/588235/9 "2011-07-07T22:37:34Z")
FWLIW, the variation to create dates using the dd/mm/yy format as per the civilised world is: =VALUE(“01/”&LEFT(A3,LEN(A3)-2)&"/"&RIGHT(A3,2)) Same assumptions on YY format apply. ---
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [July 7, 2011, 10:40pm UTC](https://boards.straightdope.com/t/excel-date-formatting/588235/10 "2011-07-07T22:40:50Z")
Thanks, everyone. I’ll try it next month.