Excel - function or formula or rmacro or vb? 1

[GeoCan]

Ok the scenario is:

Column F Class Date (date of next class)

Column G Date Books Need to be Ordered (Wed approx, 2 weeks prior to class)

So no matter which day of the week the class is on (Mon-Sat); books have to be ordered Wed, two weeks prior.

I.E.

col F = 2/14/04
col G = should read 1/28/04

col F = 2/18/04
col G = should read 2/4/04

Col F = 2/20/04
col G = should read 2/4/04

Macro preferred vice VB but what the H--, might as well learn something new, (as long as it is easy!)

 

[SkipVought]

Hi,

=(INT((F1-1)/7)-2)*7+4

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[GeoCan]

Works Great, now I'll try to figure out how, why & what it does.

Appreciate the time!

 

[SkipVought]

=(INT((F1-1)/7)-2)*7+4

Well if you take the INTEGER of DATE divided by 7, you have a value that represents a week. You adjust the day that the week starts with (I subtracted one day)

I subtract 2 weeks (your spec) then multiply the result by 7 to convert back to a date.

I add 4 to adjust the day to Wed.
BTW, in case you have not figured it out, Excel dates are just numbers representing days from 1/1/1900.

;-)

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[GeoCan]

Well I'm glad I read your post. I knew about the serial number "dates" but the Integer to Date was new. I'd still be wallowing around trying to sort it out.

Thanks for the explantion.