# Combining date and time columns

Options
✭✭✭✭

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.

Options

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.

• ✭✭✭✭✭✭
Options

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

Options

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.

• ✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• edited 03/26/21
Options

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.

• ✭✭✭✭✭✭
Options

@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)

• Options

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))

• ✭✭✭✭✭✭
Options

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))

• Options

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

• ✭✭✭✭✭✭
Options

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)

• Options

Success!!! Thank you very much!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• Options

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

Invoice_NA_ACME_2021-03-31.

• ✭✭✭✭✭✭
Options

="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)

• Options

Phew.. Paul Newcome saves the day yet again.

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!