• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula Challenge 004 - Add and subtract days to pre-1900 dates

Luke M

Excel Ninja
Staff member
Challenge name:

Add and subtract days to pre-1900 dates

Challenge Description:

* This is a formula challenge, designed to get you thinking creatively about how to solve a tricky problem using formulas only.

* Take the date in column A, add/subtract the number of days in col B, return correct date

* For simplicity, assume all dates use mm/dd/yyyy format, and all dates are greater than year 0.

* You should not use any helper cells, intermediate formulas, named ranges, or VBA

* The shortest formula I've found to do this is 160 characters, including the = sign.

* To find the length of your formula, put an apostrophe in front of the = sign, and then point a LEN function at it.(Note that the LEN function doesn't count the apostrophe)

Challenge Data


Code:
Pre-1900 date    Delta    New Date
01/06/1885    30    02/05/1885
02/07/1700    15    02/22/1700
03/01/1601    -1    02/28/1601
01/01/1492    -7    12/25/1491
01/01/1000    -5    12/27/0999
 
Last edited:
Hi Luke ,


I assume your formula does not take into account the switch from the Julian to the Gregorian calendar ; or does it ?


I assume the pre-1900 dates are all strings.


I have not yet worked out the formula , but the straightforward way to do this ( not considering the switch from Julian to Gregorian ) would be to add 1000 to the pre-1900 year , do the arithmetic , and then subtract 1000 once more.


Putting :


excel pre-1900 date


in Google throws up a lot of results.


Narayan
 
Correct, does not account for switch. Assume Gregorian calendar (aka, include leap years)

Yes, all pre-1900 dates are strings to start. Output does not have to be a string, but probably will be.

Yes, there's plenty of results on Google. I look at these challenges at a chance for people to either a) stretch their minds and learn how to solve these using functions they know, or b) learn how to use a search engine and learn not to post threads asking questions that have already been solved. =)
 
One meek way to do it


C1=LEFT(TEXT(B1+YYY,"mm/dd/yyyy"),6)&TEXT(RIGHT(A1,4)-(YEAR(XXX)-YEAR(XXX+B1)),"0000")


YYY=DATEVALUE(LEFT(Sheet1!$A1,6)&MOD(RIGHT(Sheet1!$A1,4),4))


XXX=TEXT(YYY,"mm/dd/yyyy")
 
Hi Luke ,


This works , I think :


=REPLACE(TEXT(REPLACE(A1,7,1,CHAR(CODE(MID(A1,7,1))+1))+B1,"mm/dd/yyyy"),7,1,CHAR(CODE(LEFT(TEXT(REPLACE(A1,7,1,CHAR(CODE(MID(A1,7,1))+1))+B1,"yyyy"))-1)))


Narayan
 
@Narayan,

It's close, but it doesn't work for date 03/01/1600, subtract 1 (which was a leap year).


@Sam,

Nice job, that does seem to work! Took me awhile to piece it all together. =)
 
Hi Luke ,


Yes , that is because 1000 is not divisible by 400 !


Which means I will have to add 1200 to the year ; the length of the formula will increase , but it should work.


Narayan
 
Hi Luke ,


This was my first formula , which became the one I posted earlier in order to reduce the formula length.


=TEXT(MONTH(DATE(RIGHT(A1,4)+1200,LEFT(A1,2)+0,MID(A1,4,2)+0)+B1),"00") & "/" & DAY(DATE(RIGHT(A1,4)+1200,LEFT(A1,2)+0,MID(A1,4,2)+0)+B1) & "/" & TEXT(YEAR(DATE(RIGHT(A1,4)+1200,LEFT(A1,2)+0,MID(A1,4,2)+0)+B1)-1200,"0000")


Narayan
 
Hi Luke ,


It just struck me that instead of adding and subtracting 1000 , we can use 2000 !


Which means the earlier formula will still work :


=REPLACE(TEXT(REPLACE(A1,7,1,CHAR(CODE(MID(A1,7,1))+2))+B1,"mm/dd/yyyy"),7,1,CHAR(CODE(LEFT(TEXT(REPLACE(A1,7,1,CHAR(CODE(MID(A1,7,1))+2))+B1,"yyyy"))-2)))


Narayan
 
Here's one non-elegant approach [lightly tested against posted data only]

=MONTH(DATE(MID(A2,7,4)+2000,MID(A2,1,2)+0,MID(A2,4,2)+0)+B2)&"/"&DAY(DATE(MID(A2,7,4)+2000,MID(A2,1,2)+0,MID(A2,4,2)+0)+B2)&"/"&(YEAR(DATE(MID(A2,7,4)+2000,MID(A2,1,2)+0,MID(A2,4,2)+0)+B2)-2000)
 
Nicely done Narayan & shrivallabha. Looks like you beat my original formula:

=REPLACE(TEXT(DATE(RIGHT(A6,4)+2000,LEFT(A6,2),MID(A6,4,2))+B6,"mm/dd/yyyy"),7,1,MID(TEXT(DATE(RIGHT(A6,4)+2000,LEFT(A6,2),MID(A6,4,2))+B6,"mm/dd/yyyy"),7,1)-2)
 
Here's another one


=TEXT((LEFT(A1,6)&MOD(RIGHT(A1,4),4))+B1,"mm/dd/")&TEXT(RIGHT(A1,4)+SUM(YEAR((LEFT(A1,6)&MOD(RIGHT(A1,4),4))+N(INDIRECT("RC"&{2,50},FALSE)))*{1,-1}),"0000")


It's shorter too
 
Here's one more based on Luke's idea. Few characters less :)

=REPLACE(TEXT(DATE(MID(A2,7,4)+2000,MID(A2,1,2)+0,MID(A2,4,2)+0)+B2,"MM/DD/YYYY"),7,4,YEAR(DATE(MID(A2,7,4)+2000,MID(A2,1,2)+0,MID(A2,4,2)+0)+B2)-2000)
 
Hi Sam ,


Can you please check your most recent formula with the following data :


01/01/1000 and -1


12/31/0999 and 1


Narayan
 
Hi Narayan. Sure, I just checked. Could you let me know what is it? I'm getting 12/31/0999 and 01/01/1000 as result. Not sure if I'm overlooking something!
 
Hi ShriV. In your latest formula, not sure if you intended years below 1000 to be formatted less than 4 digits!


If yes,


=TEXT((LEFT(A1,6)&MOD(RIGHT(A1,4),4))+B1,"mm/dd/")&RIGHT(A1,4)+SUM(YEAR((LEFT(A1,6)&MOD(RIGHT(A1,4),4))+N(INDIRECT("RC"&{2,50},FALSE)))*{1,-1})


should work the same way :)


Of course it is subject to getting Narayan's nod on my previous formula
 
Hi Sam ,


I am not sure because when I tested with those values , I got the following answers :


01/01/1000 and -1 gives 12/31/1000


12/31/0999 and 1 gives 01/01/0999


Narayan
 
Sam,


My initial idea was to show date as it would be. The later formula is adaptation of Luke's concept and OP [i.e. date comes out as yyyy even if it is yyy].
 
Narayan, not sure how you are getting those results. Can anybody else please check it out for both of us. From what's written in the formula, it should be giving the right answer. The formula basically takes the difference between to two years, and then adds that to the original year, and concatenates the result. That's it. Again, as I said, can someone please test it. Not sure if it has anything to do with regional settings!


Shriv, not sure if I confused you, or if I'm confused myself. I was just referring to the last formula you posted above. For year 0999, your formula gives 999, and for 0099 for example, it gives 99. I thought OP's post was to return the year in yyyy format.
 
Sam,


You spotted it sir.


My bad. Should have checked it with the 3 digit thing [just assumed it would]. Here's shorter version than one posted above.


=TEXT(DATE(MID(A2,7,4)+2000,MID(A2,1,2)+0,MID(A2,4,2)+0)+B2,"MM/DD/")&TEXT(YEAR(DATE(MID(A2,7,4)+2000,MID(A2,1,2)+0,MID(A2,4,2)+0)+B2)-2000,"0000")


Caution:= This formula assumes that your dates will always be string including the ones after 1900. Of course, I will assume that people have better options than this one for that ;)


Edit:=
I wouldn't have dared / participated something worded as challenge but enjoyed this one!
 
Here's a 107 character approach.


Note that I've changed the text to DD/MM/YYYY format in the sample data, because that's how dates are done down under. If i try to make my formula handle US dates, it does some weird type of coercion that screws things up. But should be easy enough to make minor modification that makes this work for US dates - probably just reversing the LEFT and MID bits:


=SUBSTITUTE(TEXT(DATEVALUE(LEFT(A16,2)&"/"&MID(A16,4,2)&"/"&RIGHT(A16,4)+1000)+B16,"DD/MM/YYYY"),"/2","/1")


Not sure if it works on leap years etc, and don't have time to test right now.
 
Back
Top