Day function & Excel calc time

[vijaymehta]

Hi all, I have small projection worksheet which calculates monthly employee cost based out of his start date / end date combination. This ensures I have right cost projection based on expected start/end date, but as data increases, I see noticeable increase in calculation time, which slows down worksheet for any change in date.

I was wondering if there is any better solution to this instead of nested if's and day function.

Thanks in advance.

EmpCost.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1				31-Dec-21	31-Jan-22	28-Feb-22	31-Mar-22	30-Apr-22	31-May-22	30-Jun-22	31-Jul-22	31-Aug-22	30-Sep-22	31-Oct-22	30-Nov-22	31-Dec-22
2
3	NAME	Salary	START DATE	END DATE	Jan-22 A	Feb-22 F	Mar-22 F	Apr-22 F	May-22 F	Jun-22 F	Jul-22 F	Aug-22 F	Sep-22 F	Oct-22 F	Nov-22 F	Dec-22 F
4	Employee 1	50,000	3/15/2022	9/28/2022	-	-	2,285	4,167	4,167	4,167	4,167	4,167	3,889	-	-	-
Employee Expenses

Cell Formulas
Range	Formula
F4:Q4	F4	=IF(AND($D4<=E$1+1,$E4>=F$1),$C4/12, IF(AND($D4<=F$1,$D4>E$1+1,$E4>=F$1),(DAY(F$1)-DAY($D4)+1)/DAY(F$1)*$C4/12, IF(AND($D4<=E$1,$E4>E$1,$E4<F$1),(DAY($E4))/DAY(F$1)*$C4/12, IF(AND($D4>E$1,$E4<F$1),(DAY($E4)-DAY($D4)+1)/DAY(F$1)*$C4/12,0))))

 

[kvsrinivasamurthy]

Pl change the dates E1:R1 as 1-jan 22, 1-feb 22, 1 mar 22...........etc
In F4 copied across

=MAX(0,MEDIAN(F$1-1,EOMONTH(F$1,0),$E4)-MEDIAN(F$1-1,EOMONTH(F$1,0),$D4-1))*($C4/($E4-$D4+1))

50000, I have treated amount for the duration bet ween D4 and E4. So i have divided amount by number of days E4-D4. I don't know 50000 for how many days. If it is for one year Formula should be as below.
In F4
=MAX(0,MEDIAN(F$1-1,EOMONTH(F$1,0),$E4)-MEDIAN(F$1-1,EOMONTH(F$1,0),$D4-1))*($C4/(EOMONTH($Q$1,0)-$F$1+1))

 

[vijaymehta]

Thank you Srinivas. Yes, 50000 is annual amount. I should have kept USD

However, I am somehow getting different monthly amount, like month with 31 days vis-a-vis month with 30 days. This being annual salary, in case of full month, it will be constant and not based on number of days. Any fix for that?

 

[kvsrinivasamurthy]

For the whole month it is ok. What about few days in a month . what amount is for few days.












h

 

[vijaymehta]

For the whole month it is ok. What about few days in a month . what amount is for few days.

Seem to be off for part month too. Here's snapshot of comparison:

 

[kvsrinivasamurthy]

IN F4

=IF(AND($D4<=F$1,$E4>=EOMONTH(F$1,0)),$C4/12,MAX(0,MEDIAN(F$1-1,EOMONTH(F$1,0),$E4)-MEDIAN(F$1-1,EOMONTH(F$1,0),$D4-1))*($C4/(12*DAY((EOMONTH(F$1,0))))))

 

[vijaymehta]

IN F4

=IF(AND($D4<=F$1,$E4>=EOMONTH(F$1,0)),$C4/12,MAX(0,MEDIAN(F$1-1,EOMONTH(F$1,0),$E4)-MEDIAN(F$1-1,EOMONTH(F$1,0),$D4-1))*($C4/(12*DAY((EOMONTH(F$1,0))))))

this is working perfectly. Thank you so much for your time and effort.

 

[kvsrinivasamurthy]

Thanks for feed back.