Combining date and time columns

Kate Kelley

Ideally, we would like to combine a date column and a time column, but displayed in MM/DD/YYYY format. I currently have the date column set to YYYY-MM-DD. I'm not able to figure out a formula that would transpose the dates into a second column. I was thinking some variation of a =MONTH + DAY + YEAR formula would do it, but I can't get it to work all within one cell. We're already in a huge spreadsheet and would prefer not to have to do MON DAY YEAR into three separate columns then combine.

Our desired end result is MM/DD/YYYY HH:MM in military time. I've been able to figure out the time conversion and combining the date/time into second and third columns. It's the date format that is hanging me up.

We'll be using an export of the sheet and if the date and time are in text format, that is perfect.

Johnathan Vargas Cruz

For parsing your date, assuming the date column is of type date: =MONTH([yourDateColumn]@row) + "/" + DAY([yourDateColumn]@row)+ "/" + YEAR([yourDateColumn]@row)

Then just concatenate the time to the end.

Paul Newcome

You would need to incorporate some IF statements that will output a zero before the month or day if either is less than 10.

=IF(MONTH([Date Column]@row) < 10, "0") + MONTH([Date Column]@row) + "/" + IF(DAY([Date Column]@row) < 10, "0") + DAY([Date Column]@row) + "/" + YEAR([Date Column]@row + " " + [Time Column]@row

Johnathan Vargas Cruz

For parsing your date, assuming the date column is of type date: =MONTH([yourDateColumn]@row) + "/" + DAY([yourDateColumn]@row)+ "/" + YEAR([yourDateColumn]@row)

Then just concatenate the time to the end.

Kate Kelley

Thank you for the quick response. Works almost perfectly. Is there anyway to get the leading zeros?

Using the date column, it does display as 04/02/21. When using the above formula, it converts to 4/2/2021.

Paul Newcome

You would need to incorporate some IF statements that will output a zero before the month or day if either is less than 10.

=IF(MONTH([Date Column]@row) < 10, "0") + MONTH([Date Column]@row) + "/" + IF(DAY([Date Column]@row) < 10, "0") + DAY([Date Column]@row) + "/" + YEAR([Date Column]@row + " " + [Time Column]@row

Prashant Thankappan

I need a minor variation of this formula where the result combines values from different columns to show Text_Columndata_Columndata_date in YYYY-MM-DD format and the formula I used is this:

="Invoice_" + [Invoice Number]@row + "_" + Vendor@row + "_" + IF(YEAR([Invoice Date]@row) + "-" + MONTH([Invoice Date]@row) < 10, "0") + MONTH([Invoice Date]@row) + "-" + IF(DAY([Invoice Date]@row) < 10, "0") + DAY([Invoice Date]@row)

But the outcome is Invoice_123456_Acme_M-DD. The outcome I am looking for is Invoice_123456_Acme_YY-MM-DD

Any help will be appreciated.

Paul Newcome

@Prashant Thankappan You missed the IF for the first MONTH portion.

="Invoice_" + [Invoice Number]@row + "_" + Vendor@row + "_" + IF(YEAR([Invoice Date]@row) + "-" + IF(MONTH([Invoice Date]@row) < 10, "0") + MONTH([Invoice Date]@row) + "-" + IF(DAY([Invoice Date]@row) < 10, "0") + DAY([Invoice Date]@row)

Prashant Thankappan

I tried the following and I get #Invalid Operation.

="Invoice_" + [Invoice Number]@row + "_" + Vendor@row + "_" + IF(YEAR([Invoice Date]@row) + "-" + IF(MONTH([Invoice Date]@row) < 10, "0") + MONTH([Invoice Date]@row) + "-" + IF(DAY([Invoice Date]@row) < 10, "0") + DAY([Invoice Date]@row))

Paul Newcome

My apologies. There was also an issue with the YEAR portion that I missed.

="Invoice_" + [Invoice Number]@row + "_" + Vendor@row + "_" + YEAR([Invoice Date]@row + "-" + IF(MONTH([Invoice Date]@row) < 10, "0") + MONTH([Invoice Date]@row) + "-" + IF(DAY([Invoice Date]@row) < 10, "0") + DAY([Invoice Date]@row))

Prashant Thankappan

Tried the last formula but it is still showing #Invalid Operation.

Paul Newcome

Sorry. Not sure how, but somewhere along the line a parenthesis got moved.

="Invoice_" + [Invoice Number]@row + "_" + Vendor@row + "_" + YEAR([Invoice Date]@row) + "-" + IF(MONTH([Invoice Date]@row) < 10, "0") + MONTH([Invoice Date]@row) + "-" + IF(DAY([Invoice Date]@row) < 10, "0") + DAY([Invoice Date]@row)

Prashant Thankappan

Success!!! Thank you very much!

Paul Newcome

Happy to help. 👍️

Prashant Thankappan

Sorry one more question.

I was trying to combine an if function to add NA to the column that does not have data on the column the formula is referring to.

I applied this formula:

="Invoice_" + (IF([Invoice Number]@row = "", "NA"]), ([Invoice Number]@row + "_" + Vendor@row + "_" + YEAR([Invoice Date]@row) + "-" + IF(MONTH([Invoice Date]@row) < 10, "0") + MONTH([Invoice Date]@row) + "-" + IF(DAY([Invoice Date]@row) < 10, "0") + DAY([Invoice Date]@row))))

The result of this formula is:

Invoice_NA

instead of

Invoice_NA_ACME_2021-03-31.

It cuts off the rest of it. Can you please advise?

Paul Newcome

We just need to adjust some of your parenthesis.

="Invoice_" + IF([Invoice Number]@row = "", "NA", [Invoice Number]@row) + "_" + Vendor@row + "_" + YEAR([Invoice Date]@row) + "-" + IF(MONTH([Invoice Date]@row) < 10, "0") + MONTH([Invoice Date]@row) + "-" + IF(DAY([Invoice Date]@row) < 10, "0") + DAY([Invoice Date]@row)

Prashant Thankappan

Phew.. Paul Newcome saves the day yet again.

Paul Newcome

Happy to help. 👍️