=and I need some logical help

[dannyok90]

Evening all!

Im trying to create a new spread sheet and well, iv come accross my brain block..

you can see in the spreadsheet attatched. http://s000.tinyupload.com/?file_id=84408271285290770767

Im trying to write a formula that will populate the 'F' (forcast) Column with the Weekly Spend over the weeks relative to the start and end date

i know you can do this sort of thing with =weekday and i think im onto the right sort of thing but because dates are incrementing in weeks i cant think of a way for excel to know that it needs to put the weekly spend in the columns in between the star and end date if you see what i mean, sorry i know thats pretty rubbish way of explaining!!

any help would be hugely appreciated!

Thanks Guys

 

[C with no eyes]

Oh, and to make sure the start date is always a Friday there are two solutions which jump out at me/

Either conditional format D3 to fill in red when formula =WEEKDAY(D3,2)<>5
Or use custom data validation D3 to be formula =WEEKDAY(D4,2)=5

Both of these can be dragged down and it will copy the formatting too

 

[dannyok90]

Found out why. Take your formula in F3 =IF(AND(H$1>=$D3,H$1<=$E3),$G3,"")

Your AND conditions states if it's more/less than or equal to the column date then put it in.

If you change it to =IF(AND(H$1>=$D3,H$1<$E3),$G3,"") and drag

This will then show it in the first column H (same as the start date)

If you change it to =IF(AND(H$1>$D3,H$1<=$E3),$G3,"") and drag

This will then show it in the second column I (same as the end date)

Also worth pointing out that the dates 03/02/17 to 10/02/17 spans 8 days i.e. Friday to Friday inclusive.

If you keep the formula as it is and just add -1 to your formula in the finish date i.e. in cell E3 put =D3+7*F3-1

Hi, Thanks for your help!

I took your advice and i actually just changed the date formula to minus the day and that corrected the issue about double dating things!

thanks again,
Dan

 

[C with no eyes]

Happy to help. Did you see my next post about the conditional formatting or data validation?

 

[dannyok90]

Happy to help. Did you see my next post about the conditional formatting or data validation?

Yes thankyou!

i sort of decided though that when i fill this in i should know that the first date needs to start on a friday and it can sort of became a standard operating procedure for whoever else might use it (and by that i mean my dad haha)

what do you think to this? im trying to add two values but its returning a #VALUE ! if one of them isnt there

 

[dannyok90]

well i worked that out but now when i skip a week its displaying a #value ! argghhh

 

[C with no eyes]

Which cells are H38 and J37?

Are you sure they are correct? (At first glance it looks like they should be H38 & J38, H37 and J37, H37 and H38 or J37 and J38...)

What figure are you expecting in there?

=IF(H38="",J37,H38+J37)
So far you have: If H38 is blank then have the same value as J37. Otherwise, add H38 and J37.