Filling in a cell based on another cells date criteria
 

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

Filling in a cell based on another cells date criteria
 

This should get you going in the right direction:

=IF(WEEKDAY(C1)=7,C1+2,IF(WEEKDAY(C1)=1,C1+1,C1))


This tests the weekday value of the date in C1 - if it is Saturday(7) it adds
2 days to C1, a Sunday (1) adds i day elase it accepts the date.

I think that you need the analysis pack to use the expression weekday

edvwvw

cmatera wrote:
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

--
Message posted via http://www.officekb.com

Filling in a cell based on another cells date criteria
 

But how does that test the month portion of the cell - or would this
be nested in another if statement?

On Aug 21, 8:28*am, "edvwvw via OfficeKB.com" <u42512@uwe wrote:
This should get you going in the right direction:

=IF(WEEKDAY(C1)=7,C1+2,IF(WEEKDAY(C1)=1,C1+1,C1))

This tests the weekday value of the date in C1 - if it is Saturday(7) it adds
2 days to C1, a Sunday (1) adds i day elase it accepts the date.

I think that you need the analysis pack to use the expression weekday

edvwvw



cmatera wrote:
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

--
Message posted viahttp://www.officekb.com

Filling in a cell based on another cells date criteria
 

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(YEA R(F5),MONTH(F5),1)),1,0,0,0,0,0,2))

--
__________________________________
HTH

Bob

"cmatera" wrote in message
...
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

Filling in a cell based on another cells date criteria
 

It is not necessary to check the month:

Put the formula in D1 then change the date in C1 - D1 will give you the first
day of the month if it is a weekday or the first Monday if it is a weekend
day.

edvwvw

cmatera wrote:
But how does that test the month portion of the cell - or would this
be nested in another if statement?

This should get you going in the right direction:

[quoted text clipped - 30 lines]
--
Message posted viahttp://www.officekb.com

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200808/1

Filling in a cell based on another cells date criteria
 

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?



On Aug 21, 9:01*am, "Bob Phillips" wrote:
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(YEA R(F5),MONTH(F5),1)),1,0,0,0,0,0,2))

--
__________________________________
HTH

Bob

"cmatera" wrote in message

...

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