calculate points for irregular dates

Copper Contributor

I am trying to figure out the points if the person start date is previous than 08-04-19, I want to leave them as blank or N/A automatically. 

for example, ID# 31025 start date is 02-19-19, but I have data from 08-04-19. so I want this person's point leave as N/A or blank. I tried to use IF function but I don't know how. 

Also, I am trying to figure out if the person start date + 30 days is bigger than the earliest date which is 02-16-20, I don't want to calculate those person because I don't have their all points yet. 

Please help me how to figure out these problems. 

Thank you so much!

 

5 Replies
First one: =IF(start date cell<DATE(2019,8,4), "", start date cell)

Second one: =(start date cell + 30)>DATE(2020,2,16)

@Savia Is is possible to do in one cell for this two formula? so then I can apply for all cell?

Yes - can you explain a little more exactly what you want?

@Savia 

If you go to my excel file, there is already formula in column E,F, and G which is sumifs function. 

I want to apply your formula with sumif function and then I can just drag and automatically calculate. 

I still don't understand your criteria.

If you want to make the rules you described as SUMIFS ranges / criteria, they would be:

=SUMIFS(sum range, date range, ">&DATE(2019,8,4))
=SUMIFS(sum range, date range, ">"&(DATE(2020,2,16)-30))