Wikipedia:Reference desk/Archives/Computing/2018 April 12

Welcome to the Wikipedia Computing Reference Desk Archives
The page you are currently viewing is an archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages.

April 12

Formula in Microsoft Excel for same dates in consecutive months

Is there any type of formula in Microsoft Excel that will accomplish what I am seeking? (Hopefully, a relatively simple formula, if possible.) I'd like to start with a date. Let's say, as an example, April 12, 2018. Then, I want to create a list (in a column) of all of the same dates that follow in the subsequent months. So, in my example, these would be: May 12, 2018; June 12, 2018; July 12, 2018; and so on. If I have a formula that adds 29 or 30 or 31 days to the initial formula (date), that is always "off" (since each month has a variable amount of days in it). So, I'd like a formula that will always list the 12th of each month (in my example). I want to create a column with many entries that will always list the 12th of each month for the next few years. And, I'd need a single column of cells formatted as a date (as opposed to say, three distinct columns: one for "month", one for "day"; and one for "year"; where I can simply type in a "12" for the "day" column entries). Thanks. Joseph A. Spadaro (talk) 13:16, 12 April 2018 (UTC)

Yes, you can modify the DATE function to do this. For example, if you enter 4/12 (as in your example) into cell A1, you can enter =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) into cell A2. If you drag the formula down, it will continue filling in the 12th of the month for each month and will handle the year change as well. Matt Deres (talk) 13:25, 12 April 2018 (UTC)

A-ha. Thanks. Perfect. Can you explain to me in plain English what exactly is that formula doing? I tried a "normal" date. And it works great. Thanks. Then, I tried an "odd" date. I used January 31, 2018, knowing that there would be no such thing as February 31, 2018. And it gave strange results. I expected to get some type of "error" (such as when you divide by zero in Excel or such). Rather, I did get valid dates. But they had unexpected values. Thanks. Joseph A. Spadaro (talk) 13:45, 12 April 2018 (UTC)

You mean, how it shows as the 3rd of the month from then on? The issue isn't so much with the formula (I can walk it through for you if you'd like, but it's nothing very complicated), it's with the fact that Excel doesn't actually know dates. Excel keeps track of dates using a serial number, starting with January 1, 1900 (as date number "1") and counting up from that. April 12, 2018 is serial date number 43202 and January 31, 2018 is 43131. Excel is attempting to add the appropriate number of days in the following month (i.e. to find the 31st of February) and it does that by adding values to the serial number. However, adding 31 to 43131 gets you to 43162, which is the serial number for March 3rd, 2018. It would be possible to write a series of nested IF statements that cleaned up those kinds of values to force them to, say, the last of the month or something, but it would be a complicated task. Matt Deres (talk) 14:02, 12 April 2018 (UTC)

Thanks. I see. A quick side question. You said that: Excel keeps track of dates using a serial number, starting with January 1, 1900 (as date number "1"). Does this mean that Excel cannot work with dates prior to January 1, 1900? Or are those dates codified in Excel as negative numbers? So, for example: let's take December 31, 1899. Does that date not work or function in Excel at all? Or does it work like any other date, with a serial number of negative 1 or such? Thanks. Joseph A. Spadaro (talk) 14:27, 12 April 2018 (UTC)

You're obviously using Excel right now - what did you find when you tried? :-) Short version: no. Longer version: yes, but you need to insert some VB programming to make it work. Here is Microsoft's official workaround for it: here. As you can see, it is not even a quick bit of script. Rather disappointing that it can't be done natively, really, as it seems like it should be straightforward (skip the zero and count backwards with negatives) and, given that there are more than a million g-hits for excel date prior to 1900, it seems like there's a need for it. Matt Deres (talk) 16:11, 12 April 2018 (UTC)

Thanks. I took a look at that page that you linked. It states: Microsoft Excel date formulas can only use dates entered between 1/1/1900 and 12/31/9999. It seems quite odd that Excel can utilize dates up until the year 9999 (which, pragmatically, will have little use). But, Excel cannot work with dates in the 1800's or 1700's, etc. (which would be more useful to Excel users). How odd. Thanks! Joseph A. Spadaro (talk) 02:38, 13 April 2018 (UTC)

And only the dates starting 1 March 1900 are usable if you require the day. According to Excel 1 January 1900 was a Sunday but it is actually a Monday. That continues until 29 February 1900 (it wasn't a leap year, see Leap year bug, last entry) and 1 March 1900 is correctly given as a Thursday. CambridgeBayWeather, Uqaqtuq (talk), Sunasuttuq 00:49, 14 April 2018 (UTC)

Thanks. That's crazy. You'd think that these computer tech people would be able to fix all of this. Joseph A. Spadaro (talk) 09:20, 14 April 2018 (UTC)

As Year 1900 problem#Microsoft Excel and the earlier linked article mention, Excel's leap year behaviour for the year 1900 was intentionally implemented for compatibility reasons. People often fault Lotus 1-2-3, but a commentator at the end of this thread [1] suggests that Lotus 1-2-3 was just maintaining a bug from VisiCalc. Either way this behaviour is even codified in the ISO and IEC standard Office Open XML, not without some minor controversy (I mean specifically relating to this bug, not the widespread controversy over the standard itself). [2] While there are ways this could be handled differently, it doesn't seem very likely it's going to happen. I mean after all, it was evidently only in Excel 2011 for Mac [3] that they moved to using 1900 instead of 1904 based dates, and the OOXML also supports both date formats. I wouldn't be surprised if this bug is one reason why Microsoft never bothered to update Excel to support dates before 1900. Nil Einne (talk) 10:49, 16 April 2018 (UTC)

It seems like a (somewhat) easy "fix" to go back and add in dates prior to 1900. And such dates would have great utility. I imagine there is little to no utility for dates well beyond today, such as the year 9999 and such. Joseph A. Spadaro (talk) 16:49, 17 April 2018 (UTC)

Thanks, all. Joseph A. Spadaro (talk) 16:10, 15 April 2018 (UTC)