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

Rod Finch

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.

Admin@PAT

There is a Join Function that may help with this.

J. Craig Williams

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

Admin@PAT

To show Year Day of TODAY use this:

=YEARDAY(TODAY())