Hello
I need to build a sheet for our production people to use that generates a
code date from an entered date in another cell. In one customer's case, I
need to generate a Julian date, but they only use the last two numbers of the
Julian date. The formula I use for regular Julian date generation is :

=TEXT(C160,)&TEXT((C160-DATEVALUE("1/1/"&TEXT(C160,"yy"))+1),"000")

If I simply delete the first zero in the "000", I only get 2 digits up to
99, but at 100 all three digits are indicated.

Thank you in advance!

I not usre what you are asking but I think you want to use a mudular 100
function in your code

=Mod(123,100)


so
from
C160-DATEVALUE("1/1/"&TEXT(C160,"yy"))+1

to
mod(C160-DATEVALUE("1/1/"&TEXT(C160,"yy"))+1,100)

"waybomb" wrote:

Hello
I need to build a sheet for our production people to use that generates a
code date from an entered date in another cell. In one customer's case, I
need to generate a Julian date, but they only use the last two numbers of the
Julian date. The formula I use for regular Julian date generation is :

=TEXT(C160,)&TEXT((C160-DATEVALUE("1/1/"&TEXT(C160,"yy"))+1),"000")

If I simply delete the first zero in the "000", I only get 2 digits up to
99, but at 100 all three digits are indicated.

Thank you in advance!

Hi Joel

The reference cell has today's date in it. With the forumula provided, the
formula cell returns 1/22/00.

What I am looking for is the return of the Julian date (the day's number out
of the 365 days of the year) of 22.

If I type in 6/22/09 into the reference cell, I want the Julian date
returned in the formula cell that only has the last two numbers of the Julian
date. With the formula I posted , the return is 173. But I only want 73 to
be retuned.

If I change the "000" in the forumla I posted to "00", I get only two
numbers shown at or below 99, but as soon as the number is 100 or larger, all
three numbers show up.

Thanks for working with me on this.

=Mod(173,100) will result in 73

"waybomb" wrote:

Hi Joel

The reference cell has today's date in it. With the forumula provided, the
formula cell returns 1/22/00.

What I am looking for is the return of the Julian date (the day's number out
of the 365 days of the year) of 22.

If I type in 6/22/09 into the reference cell, I want the Julian date
returned in the formula cell that only has the last two numbers of the Julian
date. With the formula I posted , the return is 173. But I only want 73 to
be retuned.

If I change the "000" in the forumla I posted to "00", I get only two
numbers shown at or below 99, but as soon as the number is 100 or larger, all
three numbers show up.

Thanks for working with me on this.

Hi again Joel

Here's the fomula I put in now:
=TEXT(C160,)&TEXT(MOD(C160-DATEVALUE("1/1/"&TEXT(C160,"yy"))+1,100),"000")

What this returns when the reference cell is 6/22/09 is 073. All I want
returned is 73.

How do I drop the zero?

Thanks
Fred

Hi Joel

Disregard that last reply. I figured it out. here's my new fomula that works!
=TEXT(C160,)&TEXT(MOD(C160-DATEVALUE("1/1/"&TEXT(C160,"yy"))+1,100),"00")


THANKS!

This is a little shorter and uses less function calls...

=MOD(1+C16-DATE(YEAR(C16),1,1),100)

--
Rick (MVP - Excel)


"waybomb" wrote in message
...
Hi Joel

Disregard that last reply. I figured it out. here's my new fomula that
works!
=TEXT(C160,)&TEXT(MOD(C160-DATEVALUE("1/1/"&TEXT(C160,"yy"))+1,100),"00")


THANKS!

Hi Rick

I tried that, but if the three digit Julain ends in 01, 02, 03, etc, all
that is returned is 1, 2, 3, etc.

If it ends in 11, 12, 13, etc, that's what the formula returns.

I do need two numbers, even if the first is a zero.

You could Custom Format your cell(s) to show 2-digits using 00 as the format
pattern.

--
Rick (MVP - Excel)


"waybomb" wrote in message
...
Hi Rick

I tried that, but if the three digit Julain ends in 01, 02, 03, etc, all
that is returned is 1, 2, 3, etc.

If it ends in 11, 12, 13, etc, that's what the formula returns.

I do need two numbers, even if the first is a zero.

Or, instead of custom formatting (as per my other post in this sub-thread),
you could leave the cell format as General and use this formula (still
shorter than yours and also still using less function calls)...

=RIGHT(1+C16-DATE(YEAR(C16),1,1),2)

--
Rick (MVP - Excel)


"waybomb" wrote in message
...
Hi Rick

I tried that, but if the three digit Julain ends in 01, 02, 03, etc, all
that is returned is 1, 2, 3, etc.

If it ends in 11, 12, 13, etc, that's what the formula returns.

I do need two numbers, even if the first is a zero.