Bespoke Julian date code required

[rugby_nut]

Hi all,

I need to produce a Julian date code that only has the last digit of the year, followed by the days, followed by a fixed number A1234. So for example the date 14/10/2022 would be 2287A1234.
I understand this will look the same for 2022 and 2032 but this is how the company works and it will only run for around 2 years.

Additionally..... I have another challenge, I want to produce the same Julian date code from the Friday to be the same for each day in a calendar week. So for example, WK43 which is Sun 16/10/22 to Sat 22/10/22 should all produce 2294A1234, see the example image below.

Any help will be greatly received. Thank you.

 

[Joe4]

I need to produce a Julian date code that only has the last digit of the year, followed by the days, followed by a fixed number A1234. So for example the date 14/10/2022 would be 2287A1234.
I understand this will look the same for 2022 and 2032 but this is how the company works and it will only run for around 2 years.

For a date in cell A2, you can use this formula:

=RIGHT(YEAR(A2),1) & (A2-DATE(YEAR(A2),1,1)+1) & "A1234"

Note that I color-coded the 3 different pieces that make up your value so that it is easier to see how it is working.

I'll will have to think a bit on the second question. That one will be a bit tricky.

 

[Joe4]

For the second part, try this variation:

=RIGHT(YEAR(A2),1) & (A2-DATE(YEAR(A2),1,1)+7-WEEKDAY(A2)) & "A1234"

 

[rugby_nut]

Hi Joe4, thanks for your feedback. That has worked. Cheers.

 

[Joe4]

You are welcome.
Glad I was able to help!