Turning "=year" value into 2 digit number and "=yearday" value into 3 digit number.

Hi there,

With "=year()" function, I can get the year in 4 digit numbers and with "=yearday()" formula, I can get the day in 1 to 3 digit numbers.

However, I am trying to combine them into 5 digit number ("YY" + "DDD") to automatically generate julian date.

Since Smartsheet doesn't have the "=text()" function, I am not so sure how to do that.

I would very much appreciate if anyone can provide me with some feedback.

Thanks in advance.

Find more posts tagged with

Formulas

There is a Join Function that may help with this.

Try this:

=RIGHT(YEAR(Date23), 2) + IF(YEARDAY(Date23) < 10, "00", IF(YEARDAY(Date23) < 100, "0")) + YEARDAY(Date23)

for row 23 and your date column named [Date]

You'll get an error on blank dates but that is just a IFERROR() wrapper to fix.

Note that YEAR() and YEARDAY() already return text.

Hope this helps.

Craig

To show Year Day of TODAY use this:

=YEARDAY(TODAY())