Yet another Excel Formula question

November 12, 2019 7:06 AM

For the record - I know this question is stupid. I am trying to do what should be a simple Excel formula but it is stymieing me.

I have been asked to produce a spreadsheet that is just a daily checklist, with each heading saying the following:

{Calendar date}
Day 1

(next box)
{Following calendar date}
Day 2

(next box)
{Following Calendar date}
Day 3

etc. So Box 1 should read "11/11/19 Day 1", then "11/12/19 Day 2", etc.

Because the total checklist will mark 75 days, I was hoping for an Excel formula to automate this rather than manually typing it. But the combination of text string and date formula is giving me a HELL of a time. I have tried isolating the data on a separate column and then concatenating, but the date renders as a raw number and I can't figure out how to alter the formula so it yields a formatted date.

What is the formula I can render and copy across 75 cells so as to create that specific combination of number and text, with a properly formatted date?

posted by EmpressCallipygos to Computers & Internet (14 answers total)

The formula =TEXT(A1,"mm/dd/yyyy") will give the date in text format, and then you can in turn concatenate the results of that cell.
posted by Mr.Know-it-some at 7:18 AM on November 12, 2019 [1 favorite]

If I type 11/11/19 into a cell and 11/12/19 into the cell immediately below it, I can highlight those two cells together and then click and hold the little square in the bottom right corner, and drag it down that column as far as I want, and it will continue to auto-populate dates in the same format as the first two cells - 11/13/19, 11/14/19, etc.

Does this do what you're trying to do?
posted by somanyamys at 7:18 AM on November 12, 2019 [2 favorites]

A4: Date (e.g. 12/1/2019)
B4: Day (e.g. 1)
C4: =TEXT(A4,"mm/dd/yy") & " Day "& TEXT(B4,"##")
posted by alittleknowledge at 7:18 AM on November 12, 2019 [6 favorites]

This is not quite an answer so feel free to delete/ignore, but I would do one row with the date, and put the "Day #" part in the second row, then remove the lines between to make it look like one cell.
posted by something something at 7:18 AM on November 12, 2019

Found the answer here.
If your date is in A1, instead of using A1 in the CONCATENATE formula, use TEXT(A1, "dd/mm/yyyy")
posted by EndsOfInvention at 7:19 AM on November 12, 2019 [3 favorites]

sometimes over engineering this isn't worth it...

do they need to be in the same cell? because it would be easiest to do two rows (or 3 even)
row 1 Cell 1: [start date] row 1 cell2 [cell 1+1] .....drag across.... row 1 cell 75= [cell 74+1]
Row 2 Cell 1: [day 1].... drag across to cell 75

If you want them together:
Row 3: Row 1 cell1 & " "& row 2 Cell1.............drag across........... row 1 cell 75 &" " & row 2 cell 75
copy, paste as values then delete the first two rows

This should take 2 mins, and get you what you need.
posted by larthegreat at 7:19 AM on November 12, 2019 [2 favorites]

and its the same answer as alittleknowledge ...
posted by larthegreat at 7:20 AM on November 12, 2019 [1 favorite]

So I'd like to show you a way I do a lot of these things which is super basic, but flexible. It uses the concatenate function. What that does is add multiple cells or text together.

A1: "Hi, "
B1: NAME
C1: "!"

If I type =concatenate (A1, B1, C1) I would get "Hi, NAME!" minus the quotes. You can see how this is useful if you had a column that had all the dates (say A1-A75) and a column that had Day 1, Day 2, etc. (B1-B75).

You could write a formula =concatenate(A1," ", B1) and get "11/11/19 Day 1" minus the quotes. Drag down and boom done. The middle " " part is adding a space.

You can also write concatenate formulas to directly contain text.

=concatenate("Today's date is ", A1, " and it is ", B1) would read "Today's date is 11/11/19 and it is Day 1" minus the quotes.

Does this make sense? I use this all the time to automate lists I need. There are much more streamlined ways but this is very basic and I think very intuitive for a beginner.
posted by OnTheLastCastle at 7:25 AM on November 12, 2019 [3 favorites]

If you want them together:
Row 3: Row 1 cell1 & " "& row 2 Cell1.............drag across........... row 1 cell 75 &" " & row 2 cell 75
copy, paste as values then delete the first two rows

Sorry to be pedantic, but this isn't in the same solution as a littleknowledge, because the issue with this is that if you don't use the TEXT function, the dates in your row 1, even if formatted as dates (i.e. 11/12/2019) will be converted to Excel numeric dates (i.e. 43781) when concatenated with text.

So if you concatenate 11/12/2019 and Day 1 simply by stringing them together, without using TEXT, you get 43781 Day 1 and not 11/12/2019 Day 1, which is the desired outcome here. (This is the whole problem of the OP in fact.)


You could write a formula =concatenate(A1," ", B1) and get "11/11/19 Day 1" minus the quotes. Drag down and boom done. The middle " " part is adding a space.

You can also write concatenate formulas to directly contain text.

Again, sorry -- if you concatenate dates with text without using the TEXT function you get the 43781 Day 1 outcome, even if your A1 in this example is formatted as a date 11/12/2019. It's not a question of just concatenating -- it's a question of how you concatenate while preserving a properly formatted date (hence why the OP mentions "but the date renders as a raw number and I can't figure out how to alter the formula so it yields a formatted date.")

It's the problem (and solution) identified here.
posted by andrewesque at 7:31 AM on November 12, 2019 [1 favorite]

Response by poster: Andrewesque, I went to the link but I cannot tell what the solution is. Can you clarify?
posted by EmpressCallipygos at 8:41 AM on November 12, 2019

You can use the column number to offset a known date and then render the result in a single cell:

    TEXT(EOMONTH(TODAY(),-1)+COLUMN(A2),"mm/dd/yyyy") & " Day " & COLUMN(A2)

• EOMONTH(TODAY(),-1) — formula to compute your start date, in this case first day of the current month
• + COLUMN(A2) — offset by the current cell column index
• TEXT(VALUE, FORMAT) — turn the date into your text (looks like you're using mm/dd/yyyy?)
• & " Day " & COLUMN(A2) — join on the day, using the same column index trick

posted by matsho at 9:49 AM on November 12, 2019

I think the @row() function is your salvation. It returns the number of the row you are on.

To find the current day, figure the appropriate base date, e.g. 1/1/2019 is 43466. Add the row number. Adjust up or depending on the starting row number.

The Day number is just the @row() plus or minus a constant depending where you start.
posted by SemiSalt at 11:20 AM on November 12, 2019 [1 favorite]

I went to the link but I cannot tell what the solution is. Can you clarify?

The solution is that you have to use the TEXT formula to properly format the date when using CONCATENATE, like this:

A            B           C
11/12/2019   1           =CONCATENATE(TEXT(A1,"mm/dd/yyyy")," Day ",B1)
11/13/2019   2           =CONCATENATE(TEXT(A2,"mm/dd/yyyy")," Day ",B2)
11/14/2019   3           =CONCATENATE(TEXT(A3,"mm/dd/yyyy")," Day ",B3)
...          ...         ...

You can alternatively concatenate using ampersands, like this:

=TEXT(A3,"mm/dd/yyyy") & " Day " & B3

I am assuming you are using US date format here (mm/dd/yyyy).
posted by EndsOfInvention at 12:01 PM on November 12, 2019

Response by poster: I ended up using the quick-and-dirty solution larthegreat had above at the end of the day. Thanks all.
posted by EmpressCallipygos at 12:41 PM on November 12, 2019