where no spreadsheet formula has gone before

[scarfman]

In the two most recent regular entries here tagged math (which is to say, not counting Twitter mirror posts tagged math), I offered the formulae I use in my fanfiction chronology spreadsheet to relate the chronologies of the properties I cross over in my fanfiction to the screen history of Doctor Who, and provided screencaps of how they are displayed in my spreadsheet. At the time, I admitted, I didn't have a proper formula for the new stardate algorithm used in the latest Star Trek films; but, now ...

FIRST A BIT OF REVIEW

This is a screen capture reprinted from the previous entry tagged math. It will be recalled that Cell B42, called Station 0, contains a formula calculating year of Doctor Who's broadcast history, that will return an amount equal to =(DATE([year],[month],[day])-23338.75)/365.25+1; where 1.00 equals the date and time of Doctor Who's premiere, 46.455 indicates May 7, 2009 when Star Trek premiered, and 50.48 indicates May 17, 2013 when Star Trek Into Darkness will premiere. (The cell below Station 0, Station 3, returns the corresponding date/time.)

Station 1 to the right of Station 0, here Cell C42, is where the existing placeholder formula for the new stardate format has been, in the latest of several nested IF formulae; because Station 1, as discussed previously, is where all Star Treks are kept.

To quote from previously: It's tricky trying to write a formula for the latest Star Trek. First of all, the new creative team changed the stardate algorithm: now the left of the decimal is the year C.E.; and the right is the number of the day in the year: .1 for January 1, .10 for January 10, .32 for February 1, .365 for December 31 (in a non-leap year) and so forth. Second, till their second movie comes out (currently scheduled May 2013) there's only one datapoint to plot from. So for the present all I do is take the stardate from the movie and treat it as I did Enterprise [i.e., to the left of the decimal is the year C.E. and to the right is a fraction of the year].

=IF(B42>46.455,(B42-46.455)+2258.42,"")
apply number formatting for rounding to one digit

FORMATTING

What may be the first thing you notice about the appearance of the latest version of my grid in this screen capture (if it's not that the grid has increased to 3x4 from 3x3 as I speculated last time I would one day do), it's that Station 1 is no longer formatted to display to one decimal. The new stardate algorithm has stardates that display up to three decimal places. In order to accomodate that, all formatting had to be removed from the Station 1 cell, and the formula modified so that classic stardates truncate themselves to one decimal point. This makes for some irregularity in display, as we see here in the grid display for March 12, 1999 [the date of the Rowan Atkinson Red Nose Day Doctor Who sketch]:

In this capture, the formulas in Station 1 signifying Deep Space Nine and in Station 2 signifying Voyager return the same result but, since that result is a whole number, with the lack of formatting Station 1 has no trailing zero decimal and doesn't look like a stardate; not until the formula advances an even tenth:

So when the new Station 1 doesn't display trailing zero decimals for whole number stardates, they look weird. I knew this would happen, which is part of the reason I put off doing this until I had enough information on the new algorithm that I would want presented accurately, to make it worth putting up with the display anomaly.

ENTERPRISE

When I was doing away with the formatting, I decided to revise my daily use Star Trek Enterprise formula, since after all Enterprise used Gregorian calendar dates instead of stardates. Assuming the Doctor Who broadcast history year formula in Cell B42, in an unformatted cell this formula returns a 22nd century Gregorian date in the format YYMMDD:

=MOD(YEAR((B42-38.84)*365.25+DATE(2151,4,19)),100)*10000+MONTH((B42-38.84)*
365.25+DATE(2151,4,19))*100+DAY((B42-38.84)*365.25+DATE(2151,4,19)),"")

Here this formula displays October 7, 2154 in Station 1 in this capture of the grid for the date of the Enterprise series finale [even though that episode was a flashforward]:

NEW STARDATES

I referred above to getting enough information to making the display discrepancies worthwhile. The information that made the difference was the stardate of Star Trek Into Darkness, which has been given as 2259.55 in one of the trailers. So, here again from above is capture for grid of the date of the premiere of Star Trek:

And here's the capture of the grid for the date May 17, 2013 when Star Trek Into Darkness premieres:

And here's the formula that (as always, assuming Doctor Who years figure in Cell B42) returns the results in Station 1 in each of the last two captures:

=INT((B42-46.453)/4.027*(1+13/365.25)+(2258+41.5/365.25))+INT(1+365.25*MOD((B42-46.453)/4.027*(1+13/365.25)+(2258+41.5/365.25),1))*IF(INT(1+365.25*MOD((B42-46.453)/4.027*(1+13/365.25)+(2258+41.5/365.25),1))>99,0.001,IF(INT(1+365.25*MOD((B42-46.453)/4.027*(1+13/365.25)+(2258+42/365.25),1))>9,0.01,0.1))

Now, it may be important to note that, though the cell formatting has been removed from Station 1 to accomodate the new stardate algorithm, the new algorithm like the old has its issues with the inability to display trailing zero decimals. The display looks the same for, for instance, January 1 2259:

as for January 10 2259:

If there's a solution to this issue, I haven't thought of it. There would have to be a way of the format on a cell being conditional on the value in the cell and/or in another cell. If Excel even does that, it's in later versions of MSOffice which I haven't learned yet.