EXCEL: filling a cell with a complete date based on just the day?

June 10, 2015 7:45 AM

Excel question: Can I enter just a 1-31 value into one cell and have a complete date appear in a different cell based on fixed month and year values?

As part of some extensive data-entry I keep typing dates in the format YY-MM-DD, but all of the dates are from March 2015.

Is it possible (for example) to type "11" into one cell and have an adjacent cell populate with "15-03-11"?

I generally understand the syntax of excel functions but I don't really know how to best do this from scratch -- any insight will be very appreciated!

posted by chudmonkey to Computers & Internet (5 answers total)

Best answer: So assuming you are typing the "11" into cell A1 you could use in say cell A2:

=CONCATENATE("15-03-",A1)

which give you a value in cell A2 of: "15-03-11"
posted by Captain_Science at 7:56 AM on June 10, 2015 [1 favorite]

Response by poster: Apparently I could also just use =DATE(2015,3,A1)

Thanks, Captain_Science!
posted by chudmonkey at 8:00 AM on June 10, 2015 [2 favorites]

or =date(2015,3,A1) where A1 has the value.
posted by jeather at 8:01 AM on June 10, 2015 [1 favorite]

Ohh yeah that second version is way better since it keeps the date in an easier to manage date format...I just love how there are about 10 ways to do just about anything in Excel....
posted by Captain_Science at 8:05 AM on June 10, 2015 [1 favorite]

A problem with Captain_Science's initial solution is that it actually produces a text entry, not a date entry. It _looks_ the same, and it prints out the same, but it's completely different under the hood, so if you wanted to reformat the cell to display dates a different way, say, or do some processing of that column based on the dates displayed therein, or build a chart with a date axis, it won't do the right thing.

It is possible that Excel is smart enough that it preprocesses some of these to do the right thing. I am pleasantly surprised, playing around with this, to learn that if you put the text entry '4.5 or the more explicit ="4.5" into A1 (note: an initial single-quote forces cell content to be text) and the numeric entry 5 into A2, and the formula =A1+A2 into A3, the result is 9.5 instead of what I expected, which was a #VALUE! error. Likewise, if I put ="2015-06-10" into cell A1, then =MONTH(A1) gives 6. Reformatting cells with dates or numbers given as strings, however, will not reformat the date or numeric form, so there are very clear limitations and if you ever expect to have to use your dates inside of Excel as dates, jeather's solution is somewhat sounder.
posted by jackbishop at 10:43 AM on June 10, 2015 [2 favorites]