formula to show month and year based off a date xx/xx/xx

Melanie Sanders

Hi I haven't seen anything in the questions already so am hoping to get a quick answer here. I have a column named receipt date and am needing to see month and year based off of the receipt date in a separate column named mo/yr. Is it possible to create a formula for this?

Kelly Moore

Hey @gmb-nps-xofigo88916

You have an extra column in the Year function

Try this

=IF(LEN(MONTH([REQUESTED SHIPMENT DATE]@row)) = 2, MONTH([REQUESTED SHIPMENT DATE]@row) + "/" + RIGHT(YEAR([REQUESTED SHIPMENT DATE]@row), 2), "0" + MONTH([REQUESTED SHIPMENT DATE]@row) + "/" + RIGHT(YEAR([REQUESTED SHIPMENT DATE]@row), 2))

Does that help?

Kelly

Kelly Moore

Hi @gmb-nps-xofigo88916

Yes, we can create a text field that appears in the format you described. Your mo/yr column should be formatted a text/number column

If the leading zero for the month isn't important, the equation is:

=MONTH([receipt date]@row) + "/" + RIGHT(YEAR([receipt date]@row), 2)

*be sure I got the name of your date field correct

If the leading zero of the Month is important, then this formula should work

=IF(LEN(MONTH([receipt date]@row)) = 2, MONTH([receipt date]@row) + "/" + RIGHT(YEAR([receipt date]@row[receipt date]@row), 2), "0" + MONTH([receipt date]@row) + "/" + RIGHT(YEAR([receipt date]@row), 2))

If you want the year to be 4 characters then omit the Right function.

=MONTH([receipt date]@row) + "/" + YEAR([receipt date]@row)

cheers,

Kelly

Melanie Sanders

Hi Kelly,

I used this formula as per what you had above provided for xx/xx/xx and it is unparseable. Here is what I used as the column where the date is actually REQUESTED SHIP DATE.

=IF(LEN(MONTH([REQUESTED SHIPMENT DATE]@row)) = 2, MONTH([REQUESTED SHIPMENT DATE]@row) + "/" + RIGHT(YEAR([REQUESTED SHIPMENT DATE]@row[REQUESTED SHIPMENT DATE]@row), 2), "0" + MONTH([REQUESTED SHIPMENT DATE]@row) + "/" + RIGHT(YEAR([REQUESTED SHIPMENT DATE]@row), 2))

Do you have any other suggestions?

Thanks so much,

Melanie

Kelly Moore

Hey @gmb-nps-xofigo88916

You have an extra column in the Year function

Try this

=IF(LEN(MONTH([REQUESTED SHIPMENT DATE]@row)) = 2, MONTH([REQUESTED SHIPMENT DATE]@row) + "/" + RIGHT(YEAR([REQUESTED SHIPMENT DATE]@row), 2), "0" + MONTH([REQUESTED SHIPMENT DATE]@row) + "/" + RIGHT(YEAR([REQUESTED SHIPMENT DATE]@row), 2))

Does that help?

Kelly

Melanie Sanders

You are awesome....thank you so much!!