Filling in a cell based on another cells date criteria

  • Thread starter Thread starter cmatera
  • Start date Start date
C

cmatera

I want to populate a cell with the value of another
cell..based on the following date criteria - My date cell is stored in
(F5) and the cell I want to populate (D20) has a value stored in (C20)

If the first of the month is a Mon-FRI then the cell (D20) gets
populated on
the date in the week which the first of the month falls on...example:
if 8/1/08 were a wed the field (D20) would get populated with the
value in cell (C20)

UNLESS

If the first of the month were a Sat..like 11/1/08... if the first of
the month falls on a saturday
on a sat...then the field (D20) would get populated with (C20's) value
on the following monday
(11/3/08)

This is a really pesky problem - but any help is appreciated
Thanks
 
But how does that test the month portion of the cell - or would this
be nested in another if statement?
 
I must admit to being confused between F5, C20 and which date of the first,
but ...

=IF(WEEKDAY(DATE(YEAR(F5),MONTH(F5),1),2)<6,C20,
DATE(YEAR(F5),MONTH(F5),1)+CHOOSE(WEEKDAY(DATE(YEAR(F5),MONTH(F5),1)),1,0,0,0,0,0,2))
 
I'm confused too :-)

But... my dates are stored in cells F5 - G5 - H5 - I5 - J5 - K5 - L5 -
M5 - N5 - O5 - They're stored in the format MM/DD/YY
So F5 - 05 Might be:
07/28/08 (MON) 07/29/08(TUE) 07/30/08 (WED) 07/31/08 (THUR) 08/1/08
(FRI) 08/02/08 (SAT) 08/03/08 (SUN) 08/04/08 (MON) 08/05/08 (TUE)
08/06/08 (WED)

The values to populate in the cells below these dates so like F7 for
example is stored in a cell C20 - that value can change...but one
particular value is 10 (this is an hourly rate at which people earn
time)

People can only Earn time at the beginning of the month and NOT on a
Friday - so a Monday. So if the beginning of the month fell on a
Friday...(8/1/08) the Earned time would have to populate the cell
below the 8/4/08 date cell (the monday).

If the first of the month fell on a wednesday - then the earned time
would populate the cell below the wednesday date. So the formula
would have to take that into account...

Any ideas?
 
Back
Top