Converting Negative Numbers Into Dates

**artoni** · 09-02-2009, 05:45 PM

Hey everyone! New to the forum, thought I'd ask about a problem that I've been having some trouble with. I'll start at the beginning.

I have a formula that goes as follows:

Please Login or Register  to view this content.

So if the date is 9/11/2009, it'll give me '4'. This is good, I can use this! However, it the date is 9/17/2009, it'll give me '-2'. This is not so good, as I am using this to figure out a pro-rate charge, and negative numbers...not so good with this.

I tried doing something with CHOOSE/MATCH and VLOOKUP; however, as far as I can tell, these don't work with numbers on the first part. That is to say, converting -2 into 28. Or 28 into -2, for that matter. I can convert a word into either, but that's not what I'm trying to do. I'm trying to tell Excel that -1 needs to turn into 29, -2 into 28, -3 into 27, so on and so forth up to -15 meaning 15.

I'm somewhat of a novice at Excel; it may very well be that I'm making things harder on myself. I look forwards to your advice and suggestions. If anyone could advise me on how to incorporate this conversion code into the primary =SUM noted above, that would be icing on the cake.

Thank you!

**romperstomper** · 09-02-2009, 05:52 PM

How are you calculating the pro rata charge? It may well be easier to add the adjustment in there. Alternatively, rather than using a day number, you could use an actual date - the DATE function will accept a negative day number and simply step back as required. For example:
=DATE(2009,1,0)
will give 31 Dec 2008 and similarly:
=DATE(2009,1,-1)
will return 30 Dec 2008.