Get data depending on the date

[chaboyski]

Hi guys,

just wondering how i can get the data from column I and feed show it in column C depending on the date if it is less than or equal to date. i need july 31 - Aug 4 to show 994, then, aug 5-6(weekend) would be 0, then Aug 7 - 11 1235, then 0 again for Aug 12 and 13 since its weekend.

Book1
	B	C	D	E	F	G	H	I
1		Data
2	31-Jul							Clean
3	1-Aug						31-Jul	994
4	2-Aug						7-Aug	1235
5	3-Aug						14-Aug	1236
6	4-Aug						21-Aug	850
7	5-Aug						28-Aug	425
8	6-Aug						4-Sep	485
9	7-Aug
10	8-Aug
11	9-Aug
12	10-Aug
13	11-Aug
Sheet1

Cell Formulas
Range	Formula
B3:B13	B3	=B2+1

 

[Fluff]

How about

=IF(WEEKDAY(B2,2)>5,0,INDEX($I$3:$I$8,MATCH(B2,$H$3:$H$8,1)))

 

[Phuoc]

Try this:

Book1
	B	C	D				H	I
1		Data
2	31-Jul	994						Clean
3	1-Aug	994					31-Jul	994
4	2-Aug	994					7-Aug	1235
5	3-Aug	994					14-Aug	1236
6	4-Aug	994					21-Aug	850
7	5-Aug	0					28-Aug	425
8	6-Aug	0					4-Sep	485
9	7-Aug	1235
10	8-Aug	1235
11	9-Aug	1235
12	10-Aug	1235
13	11-Aug	1235
14	12-Aug	0
15	13-Aug	0
Sheet1

Cell Formulas
Range	Formula
C2:C15	C2	=IF(WEEKDAY(B2,2)>5,0,VLOOKUP(B2,$H$3:$I$8,2,1))
B3:B15	B3	=B2+1

 

[chaboyski]

thank you @Phuoc

 

[EFANYoutube]

Hey
This in C2 should help, just fill it down

=IF(OR(WEEKDAY(B2)=7,WEEKDAY(B2)=1),0,VLOOKUP(B2-(WEEKDAY(B2,2)-1),$H$3:$I$8,2,0))

 

[Fluff]

Did you try the formula I suggested?

 

[chaboyski]

Did you try the formula I suggested?

much more clearer. thank you!

 

[chaboyski]

@Fluff with the formula =IF(WEEKDAY(B2,2)>5,0,

what does the 2 means as well as the >5

 

[Fluff]

The 2 means to start the week on a Monday & >5 means anything after Friday WEEKDAY Function

 

[chaboyski]

got it. thank you!

The 2 means to start the week on a Monday & >5 means anything after Friday WEEKDAY Function