SOLVED

Calculate the points for irregular dates

Copper Contributor

Hi, I am trying to calculate the points from the hired date to 1st month, 2nd month, and 3rd month. 

There is a row for the date with points for each person. 

but the thing is the date with points are irregular. 

How can I calculate the points range from hired date to 1st month points when the date is irregular?

Then I can just drag down and will automatically calculate for the rest people points. 

Thank you

10 Replies

@chaehyun 

 

Hello, kindly upload a sample file

@Abiola1 

Hi I just upload the excel file. 

Thank you

@chaehyun 

Do you have any rules as to what those dates may be?

 

For example, can the first, second or third month dates be weekend dates?

@Patrick2788 

Yes you can set those months dates be weekend dates. 

Thanks

 

@chaehyun 

Perhaps

=SUMIFS(I3:X3,$I$2:$X$2,"<="&EDATE($B3,1))

for first month, etc.

@Sergei Baklan I tried to calculate the points from hired date (start date) to 1 month work date but it's not matching. 

For example, row 4, ID #: 31063, the point is not matching according to your formula. It should be 11 points instead of 32 points

@chaehyun 

Yes, missed starting date

=SUMIFS(I4:X4,$I$2:$X$2,"<="&EDATE($B4,1),$I$2:$X$2,">="&B4)

For row #4 it shall be 7 - between Dec 10, 19 and Jan 10, 20

@Sergei Baklan 

Oh yeah the first month is correct but I calculated the same ID person for the second month points but it should be 5 points which should not include the first month points. so 2nd month points should be from 1/10-2/10.  and 3rd month is same

best response confirmed by chaehyun (Copper Contributor)
Solution

@chaehyun 

I calculated for first month, first two months, first three months. If second month (not first two) 

=SUMIFS(I3:X3,$I$2:$X$2,"<="&EDATE($B3,2),$I$2:$X$2,">"&EDATE($B3,1))

same for third

@Sergei Baklan 

Is it possible to calculate if the person start date is previous than the date which is 08-04-19, can I leave them as a blank or N/A?

for example, ID# 31025 start date is 02-19-19 and there is no data at that time because there is no data before 08-04-19. I want to leave them as a blank or N/A. Is it possible?

Thank you so much!

1 best response

Accepted Solutions
best response confirmed by chaehyun (Copper Contributor)
Solution

@chaehyun 

I calculated for first month, first two months, first three months. If second month (not first two) 

=SUMIFS(I3:X3,$I$2:$X$2,"<="&EDATE($B3,2),$I$2:$X$2,">"&EDATE($B3,1))

same for third

View solution in original post