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
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
