Inserting dates in increments of X, based on dates in other cells

 I’m working with an old spreadsheet that has nearly 1500 rows of data.  This spreadsheet has been in existence since late 2009 and has been maintained by several different people, with no one providing Excel-minded, future-proofing leadership to unify what they were maintaining and how they were maintaining it.  As such, the data is incomplete and dirty.  Here’s how I fixed over 300 empty cells with estimated expiration dates within a few minutes (it can be done much quicker if you know exactly what you’re doing).

Solution:

=SUM((ROUNDUP(([@[Premature Closure]] – [@[Date of Assignment]]) / 30, 0) * 30) +[@[Date of Assignment]])

Explanation:

This takes the date in Premature Closure and the date in Date of Assignment and subtracts them.  Then, it divides the answer by 30 and rounds up to the nearest integer (if you use a positive number here, then your answer is rounded up to the specified number of decimal places; if you use a negative number, then your answer is rounded up to the left of the decimal point).  This will result in integers, probably small ones.  Then, the code multiplies the answer by 30 (all contracts expire in increments of 30, in my case.  Change this for your own needs) and adds the answer to the Date of Assignment.  Remember to use your own column names instead of Premature Closure, etc.

Story:

For my purposes, the expiration dates need to be in increments of 30 days.  I was working with 3 columns of dates: Date of Assignment and either Premature Closure or Date of FGC (whichever one had a date filled in).  In the Solution above, if Premature Closure was blank but Date of FGC had a date, I would simply replace [@[Premature Closure]] with [@[Date of FGC]].

At first, I thought to add several extra columns in which to do all these calculations.  I added one column to perform the ROUNDUP calculation, then passed that answer on to another column to multiply the answer by 30.  At this point I realized I could all this in one column, so I began combining all the calculations.  It took me a few tries to get exactly what I needed.

At some point I thought I needed to use modulo (%), so I tried to do 7 % 3, for instance.  Excel doesn’t do modulo that way, so I had to google how to use modulo in Excel, and turns out you use =MOD().  At that point I realized modulu is not what I need; in fact, it was the opposite of what I needed.  I was thinking of floor division, anyway.  I needed to round up the answer, so I typed =ROUND and found Excel has 3 options, =ROUND(), =ROUNDUP(), and =ROUNDDOWN().  If a contract is 1 day over the expiration date, I need it to be extended another 30 days, so of course I needed =ROUNDUP().

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s