Formula to add sums to data range in Excel

david col

New Member
Joined
Aug 27, 2016
Messages
14
Hi Would you be able to assist me with dates in excel sheet ?

I need to creates formula in the grey filled cells. It is to monitor cash flow and when money is due in. Based on the work dates column A&B I need formula in columns P,R,T etc through to BF to show the sums which are portioned to the calendar claim month the result is shown in the calendar month 2 months after the claim month.

We claim for the number of days between the date in column B and the end of that month e.g. C8 is 22 although the number of days for this as column E is only 15 days therefor the result for X8 would be D8*O8 or 2000. Its a little tricky as some dates may fall part within the first month and subsequent months. Assuming there are 22 working days within a month.

The above only applies to the rows marked Labour in column M. for the Mat element it is a bit easier as the claim date is 100% of values in column O this is claimed on anything that falls within the whole calendar month in column A so I see the formula on mat rows differ from the labour rows? Columns F- K I have added just for reference but may not be required or can hide these columns?

Please let me know if you can assist. Any helps appreciated, I can obviously copy the rows down when done.

File via link here


Many thanks
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Watch MrExcel Video

Forum statistics

Threads
1,129,378
Messages
5,635,911
Members
416,887
Latest member
SheriE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top