If you would like to unify the dates in Excel to be either in the beginning of current or next month, below formula is the solution.
Below example shows that Actual Date on 1/5/2014 (column A2) has a Controlled Date of 1/1/2014, because Jan-1st is less than 20th of the month, as specified in the formula. You can always change the threshold based on your needs.
Second record of Actual Date is 1/25/2014 (which is greater than 20th of the month), therefore, rounded up to the next month, as specified.
=IF(DAY(A2)<20,DATE(YEAR(A2),MONTH(A2),1),DATE(YEAR(A2),MONTH(A2)+1,1))
Below example shows that Actual Date on 1/5/2014 (column A2) has a Controlled Date of 1/1/2014, because Jan-1st is less than 20th of the month, as specified in the formula. You can always change the threshold based on your needs.
Second record of Actual Date is 1/25/2014 (which is greater than 20th of the month), therefore, rounded up to the next month, as specified.
=IF(DAY(A2)<20,DATE(YEAR(A2),MONTH(A2),1),DATE(YEAR(A2),MONTH(A2)+1,1))
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.