Putting equal cost for each person according to the start and end date of the range and start date and end date of the project

[Athena1030]

Hi,

I really need help urgently. I have been struggling from 4 days and I am not able to solve it. I have to find equal distribution of cost in cell H4:T4, in each range. However the cost should auto calculate itself if we change the start date and end date of the project according to no. of days worked. Cost per hour and hours per day is already given.

Can someone please tell me what formula I should put?

A	B	C	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1					Start date for FY21 range	5/31/2020	6/28/2020	7/26/2020	8/23/2020	9/20/2020	10/18/2020	11/15/2020	12/13/2020	10/1/2021	7/2/2021	7/3/2021	4/4/2021	2/5/2021
2					End date for FY 21 range	6/27/2020	7/25/2020	8/22/2020	9/19/2020	10/17/2020	11/17/2020	12/12/2020	9/1/2021	6/2/2021	6/3/2021	3/4/2021	1/5/2021	5/29/2021
3	Start Date	End Date	Cost per hour	Hours per day	Range	R1	R2	R3	R4	R5	R6	R7	R8	R9	R10	R11	R12	R13
4	1/6/2020	29/5/2021	$ 54.00	9		747	747	747
6	1/7/2020	29/5/2021	$ 4.94	9
7	1/8/2020	29/5/2021	$ 25.19	9
8	1/9/2020	29/5/2021	$ 25.19	9

 

[Peter_SSs]

Welcome to the MrExcel board!

Are you sure that your sample data is correct?
I ask because rows 1 and 2 dates seem a bit strange to me. It initially appears that you dates are in m/d/y format and columns H:L are all 28 day periods with row 1 entries equal to the row 2 entry of the column before + 1 day. But then

• Column M appears to be a 31 day period.
• In column N, N1 is not equal to M2 + 1 day
• Column O is either a 263 day period or else cell O2 is in d/m/y format not in m/d/y format??
• Actually, from cell O2 and to the right, all the cells look like they might be in d/m/y format, except cell T2 which is clearly back in m/d/y format

Very confusing!!

Can you clarify exactly what is going on with those dates.

Also suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.

 

[Athena1030]

Hi thank you for your reply. The data is correct.

I should have been more clear earlier. There are 13 ranges in the data. Each range equals to 4 weeks which becomes 20 working days (assuming Saturday and Sunday are off). All dates are in MM/DD/YY format.

Also here the financial period starts from 1st July and ends on 31st May every year. So R1 is from 31st/May/2020 - 27th/june/2020 (cell H1 and H2).

What i need to do is calculate the cost for each row from start date of joining to end date of joining range wise. For example: If a person joins a project on 10/Aug/2020 and end the project on 29/Dec/2020 so that cost will have to calculated from cells J4-O4, specific to no. of working days in range R3 and R8 from start and end date.

For simple understanding, as we have 12 months in a year, right now for this we have 13 range, each range equals 4 weeks.


Does it help for you to help me now for the solution?

 

[Athena1030]

Welcome to the MrExcel board!

Are you sure that your sample data is correct?
I ask because rows 1 and 2 dates seem a bit strange to me. It initially appears that you dates are in m/d/y format and columns H:L are all 28 day periods with row 1 entries equal to the row 2 entry of the column before + 1 day. But then

• Column M appears to be a 31 day period.
• In column N, N1 is not equal to M2 + 1 day
• Column O is either a 263 day period or else cell O2 is in d/m/y format not in m/d/y format??
• Actually, from cell O2 and to the right, all the cells look like they might be in d/m/y format, except cell T2 which is clearly back in m/d/y format

Very confusing!!

Can you clarify exactly what is going on with those dates.

Also suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.

Hi thank you for your reply. The data is correct.

I should have been more clear earlier. There are 13 ranges in the data. Each range equals to 4 weeks which becomes 20 working days (assuming Saturday and Sunday are off). All dates are in MM/DD/YY format.

Also here the financial period starts from 1st July and ends on 31st May every year. So R1 is from 31st/May/2020 - 27th/june/2020 (cell H1 and H2).

What i need to do is calculate the cost for each row from start date of joining to end date of joining range wise. For example: If a person joins a project on 10/Aug/2020 and end the project on 29/Dec/2020 so that cost will have to calculated from cells J4-O4, specific to no. of working days in range R3 and R8 from start and end date.

For simple understanding, as we have 12 months in a year, right now for this we have 13 range, each range equals 4 weeks.


Does it help for you to help me now for the solution?

 

[Peter_SSs]

The data is correct.

In that case your dates in rows 1 & 2 must be in m/d/y format since the first date (H1) shows 5/31/2020 which must be 31 May 2020.

Here are your dates copied form post #1 and formatted as dd-mmm-yy and in row 3 the days between the 2 dates. It makes no sense to me.

Athena1030 2020-06-12 1.xlsm
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Start date for FY21 range	31-May-20	28-Jun-20	26-Jul-20	23-Aug-20	20-Sep-20	18-Oct-20	15-Nov-20	13-Dec-20	01-Oct-21	02-Jul-21	03-Jul-21	04-Apr-21	05-Feb-21
2	End date for FY 21 range	27-Jun-20	25-Jul-20	22-Aug-20	19-Sep-20	17-Oct-20	17-Nov-20	12-Dec-20	01-Sep-21	02-Jun-21	03-Jun-21	04-Mar-21	05-Jan-21	29-May-21
3	Days in range	28	28	28	28	28	31	28	263	-120	-28	-120	-88	114
Sheet2

Cell Formulas
Range	Formula
H3:T3	H3	=H2-H1+1