Automatacally Select a Julian Date

[johnnyb5]

I’ve developed a formula which calculates values base on Julian Dates. The formula is working fine except I must manually change the Julian date cell reference monthly. I’d like some help to find a way to automatically select the correct Julian date reference from my list.

The formula is:
=IF(VALUE(RIGHT($D2,"3"))<'REP DAY'!$A$9, VALUE(RIGHT($G2,"3"))-'REP DAY'!$A$9, VALUE(RIGHT($G2,"3"))-VALUE(RIGHT($D2,"3")))

The Julian date reference is: 'REP DAY'!$A$9

On the REP DAY Sheet is a header in A1, and 12 monthly dates in A2: A13; 16, 47, 75, 106, 136, 167, 197, 228, 259, 289, 320, 350. This list lacks the two digit year prefix (so that I can use it from year to year).

Last month I used the cell reference A8 and next month I will change it again to A10, currently I’m using A9 as shown in the formula.

The variable to determine which date to use is the current Julian date listed in Column G of the current active sheet where the formula is located. So if the current Julian date in cell G2 was 15240 I want it to select the equal value or closest smaller date. Therefore 15240 would select 228.

Does anyone know how to select a value equal to or the next smaller one in a list than the Julian Date listed in G2?

 

[AlphaFrog]

Try this...

=LOOKUP(MOD(G2,1000),'REP DAY'!A2:A13)

If G2 can have a value < 15016, then add a 0 to the top of the REP DAY'!A2:A14 list.

 

[johnnyb5]

Alpha Frog,

It works great!!! It took a few minutes for me to remember to add the $ signs so when I drug it down the correct cells were searched. I've used vlookup before but I've never used just lookup and I thought this was very interesting.
You're time and expertise is appreciated!
Thanks again,
JB

 

[AlphaFrog]

Alpha Frog,

It works great!!! It took a few minutes for me to remember to add the $ signs so when I drug it down the correct cells were searched. I've used vlookup before but I've never used just lookup and I thought this was very interesting.
You're time and expertise is appreciated!
Thanks again,
JB

You're welcome. Thanks for the feedback.

You could use VLookup as well.
=VLOOKUP(MOD(G2,1000),'REP DAY'!A2:A13,1,1)

 

[johnnyb5]

Thanks for suggesting another option. I did add the "0" at the beginning of the REP DAY list. This is what the formula looks like now: =IF(VALUE(RIGHT($D2,"3"))<LOOKUP(MOD($G2,1000),'REP DAY'!$A$2:$A$14), VALUE(RIGHT($G2,"3"))-LOOKUP(MOD($G2,1000),'REP DAY'!$A$2:$A$14), VALUE(RIGHT($G2,"3"))-VALUE(RIGHT($D2,"3")))

Could I trouble you for a minute to explain the "MOD($G2,1000)" as that's new to me as well.

Thanks,
JB

 

[johnnyb5]

Looks like some got cut off...
formula: =IF(VALUE(RIGHT($D2,"3"))<LOOKUP(MOD($G2,1000),'REP DAY'!$A$2:$A$14), VALUE(RIGHT($G2,"3"))-LOOKUP(MOD($G2,1000),'REP DAY'!$A$2:$A$14), VALUE(RIGHT($G2,"3"))-VALUE(RIGHT($D2,"3")))

 

[AlphaFrog]

Thanks for suggesting another option. I did add the "0" at the beginning of the REP DAY list. This is what the formula looks like now: =IF(VALUE(RIGHT($D2,"3"))<lookup(mod($g2,1000),'rep day'!$a$2:$a$14),="" value(right($g2,"3"))-lookup(mod($g2,1000),'rep="" value(right($g2,"3"))-value(right($d2,"3")))

Could I trouble you for a minute to explain the "MOD($G2,1000)" as that's new to me as well.

Thanks,
JB

It returns the remainder of G2/1000 or in this case the value of last three digits of G2

EDIT: In the formula, add spaces around < and > otherwise it looks like HTML code.


</lookup(mod($g2,1000),'rep>

 

[johnnyb5]

AlphaFrog, Once again, thanks for the tips. My replies appear to be getting cut off so I'll keep it short. I appreciate your help!
JB

 

[johnnyb5]

Spaces added to Formula less than symbol: =IF(VALUE(RIGHT($D2,"3")) < LOOKUP(MOD($G2,1000),'REP DAY'!$A$2:$A$14), VALUE(RIGHT($G2,"3"))-LOOKUP(MOD($G2,1000),'REP DAY'!$A$2:$A$14), VALUE(RIGHT($G2,"3"))-VALUE(RIGHT($D2,"3")))

 

[Rick Rothstein]

Looks like some got cut off...
formula: =IF(VALUE(RIGHT($D2,"3"))<LOOKUP(MOD($G2,1000),'REP DAY'!$A$2:$A$14), VALUE(RIGHT($G2,"3"))-LOOKUP(MOD($G2,1000),'REP DAY'!$A$2:$A$14), VALUE(RIGHT($G2,"3"))-VALUE(RIGHT($D2,"3")))

The problem is this forum's comment processor sees less than symbol followed by a letter as the start of an HTML tag... to overcome that, simply put a space between the less than symbol and the letter...

=IF(VALUE(RIGHT($D2,"3"))< LOOKUP(MOD($G2,1000),'REP DAY'!$A$2:$A$14), VALUE(RIGHT($G2,"3"))-LOOKUP(MOD($G2,1000),'REP DAY'!$A$2:$A$14), VALUE(RIGHT($G2,"3"))-VALUE(RIGHT($D2,"3")))