Feb 24 2020 08:04 AM - edited Feb 24 2020 08:15 AM
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
Feb 24 2020 08:05 AM
Feb 24 2020 08:16 AM
Feb 24 2020 08:22 AM
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?
Feb 24 2020 08:25 AM
Feb 24 2020 08:29 AM
Feb 24 2020 08:35 AM
@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
Feb 24 2020 08:43 AM
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
Feb 24 2020 08:49 AM
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
Feb 24 2020 08:55 AM
SolutionI 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
Feb 24 2020 01:24 PM
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!
Feb 24 2020 08:55 AM
SolutionI 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