# Formatting in Formula Cells

Options
edited 05/09/24

I have two date cells that I have formatted as long dates (May 30, 2018) titled Contract_Begin and Contract_End. I want the formula cell to read contract begin date through contract end date (for ex: May 30, 2024 through August 10, 2024). However, the cell formats to 5/30/24 through 8/10/24. I posted the formula I am using below.

=[Contract_Begin]@row + " through " + [Contract_End]@row

I have a similar issue with a cell I have formatted as currency (titled Orientation Stipend). It goes from \$1,500 to 1500 in the formula cell. I also posted the formula I am using below. Right now, I have the "\$" and "," in the formula so that it formats correctly but want to find a solution in case I have a number that has 2 digits to the left (like \$10,000).

="This includes \$" + LEFT([Orientation Stipend]@row,1) + "," + RIGHT([Orientation Stipend]@row,3) + " for one-time orientation of 40 hours to cover agency orientation, course meetings, and course preparation."

Tags:

• ✭✭✭✭
Options

For the Contract Date, there is no easy way, but there are ways. These are steps to get this.

1. Create a CalendarMonth sheet through 12 December. This sheet is used to lookup the MonthText from the month number.

2. Create the following columns in your sheet

Create Month Begin column

=MONTH([Contract_Begin]@row)

Create Month End column

=MONTH([Contract_End]@row)

Create Month Begin Name column

=VLOOKUP([Month Begin]@row, {CalendarMonth MonthText}, 2, false)

I'm using VLookup but you could also use Index Match

Create Month End Name column

=VLOOKUP([Month End]@row, {CalendarMonth MonthText}, 2, false)

Create Contract Begin Date column

=[Month Begin Name]@row + " ," + DAY([Contract_Begin]@row) + ", " + YEAR([Contract_Begin]@row)

Create Contract End Date column

=[Month End Name]@row + " ," + DAY([Contract_End]@row) + ", " + YEAR([Contract_End]@row)

Create Contract Period column

=[Contract Begin Date]@row + " through " + [Contract End Date]@row

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!