How to populate valid resource rates from a source table, for each week of a project.

r_schwab

I have a Resource Table with…

Resource ID	Name	DailyRate	ValidFrom
R01	Resource X	$1,500	3/8/25
R01	Resource X	$2,000	9/1/25
R01	Resource X	$2,200	9/31/25

I also have a Week by Resource Table with…

WeekID	ResourceID	WCDate	ValidRatePerWeek
W01	R01	3/2/25	auto-pop $1,500
W02 to W26	R01	All the dates for W02 to W26	auto-pop $1,500
W27	R01	8/31/25	auto-pop $2,000

How do I auto-populate the ValidRatePerWeek in the 2nd table?

I want it to look up the Resource Table and find the Daily Rate valid for that week.

I'm in Australia but I've changed my dates to be US format for the example.

Hope my tables make sense.

jjg279

Howdy @r_schwab, would a use of the "weeknumber" function work for you? Something like:

=SUMIF([ValidFrom]:[ValidFrom], WEEKNUMBER(@cell ) = "your week number*", [DailyRate]:[DailyRate])

*could use either a reference date aka = WEEKNUMBER(WCDate@row) or put in a number. For your ranges you could use SUMIFS instead:

=SUMIFS([DailyRate]:[DailyRate], AND(WEEKNUMBER(@cell) >= "your weeknumber", WEEKNUMBER(@cell) <= "your weeknumber"), [ValidFrom]:[ValidFrom])