Formatting in Formula Cells

hturazzo

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."

dojones

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