Paul Gadzikowski

• Thu, 17:45: Drew two 3Faces today (and a Creative Process) in hopes of also drawing two tomorrow and having a weekend buffer again
• Thu, 18:18: Hero of Three Faces update: Kirk remembers this come physical time. https://t.co/pDwOhMPaFa #startrek
• Fri, 05:09: Creative Process update: alot. https://t.co/2BWdtlbRua #webcomics #CreativeProcess
• Fri, 11:46: yesterday i updated the formulas in my spreadsheet that count time by doctor to start counting fourteen after the coming xmas special

• Tue, 17:20: My daily use spreadsheet file got corrupted. Before I realized the issue was the file, not the program, I overwrote the weekly backup. ...
• Tue, 17:20: ... Now I'll have to rebuild it from a copy that's four or five months old. ...
• Tue, 17:20: ... This is the spreadsheet with my crossover chronology formula grid, my lists of webcomics drawn, my daily ticklers, etc. ...
• Tue, 17:21: ... Rebuilding from a five month old backup is still better than starting from scratch, at least for the webcomic lists.
• Tue, 17:56: RT @amaditalks: CPS investigations are also, by law, confidential. So think hard about why we even know that this is happening. Somebody is talking trash.
• Tue, 20:36: Congestion's moved from my head to my chest. It's like what happens to me some nights for two hours, but all day.
• Tue, 20:43: Actually hasn't been happening at night much since the last time, cuz I noticed I'd slept on my right, and started sleeping on my left.
• Tue, 23:21: prokopetz: prokopetz: Rape is the only crime on the books for which arguing that the temptation to commit... http://t.co/3VA6Bj4VZT
• Wed, 06:37: amaezingjew: dreamlessdelirium: dynastylnoire: sktagg23: ferenginar: yungmethuselah: If you think all Black... http://t.co/EyhEIkKhWc
• Wed, 06:44: RT @jaythenerdkid: apparently I should become one of those social media marketing experts
  
  my advice: swear a lot, post selfies, don't listen to "experts"
• Wed, 08:19: Black people having safe spaces where white people are not welcome is not (“reverse”-)racism for the same... http://t.co/o5qpWRSQSu

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.

• Thu, 12:07: Thought the tablet had stopped working. No, I just had the stylus backwards in my hand.
• Thu, 12:10: The 3Faces I wanna draw today is a conversion of an old KAITAS gag. Usually doctoring those is fun, but this one needs outright redrawing.
• Thu, 12:11: Apparently in art as well as in prose, I like editing more than creating.
• Thu, 13:56: 3faces done. Dishwasher changed. Just need to complete weekly backup and I can head out for T-dinner.
• Thu, 21:49: #AKOTAS update: way to work the system, Mordred. http://t.co/U9b9HkSU #webcomics #kingarthur
• Thu, 22:20: Looks like I picked the right day to be away from Tumblr for seven hours.
• Thu, 22:22: Hero of Three Faces update: very specific anomalies. http://t.co/TdTVvw8r #startrek #enterprise #doctorwho
• Fri, 08:51: Creative Process update: holiday. http://t.co/QmSuLLrP #webcomics #CreativeProcess
• Fri, 09:06: Today the formula in my fanfiction chronology spreadsheet which tracks which year of #DoctorWho it is has rolled over to 50.
• Fri, 10:53: =(NOW()-23338.75)/365.25+1 - This is a MicroSoft Excel formula. This formula in a spreadsheet cell returns th… http://t.co/1EQGjC77
• Fri, 11:03: My keyboard won't render certain uppercase letters - E, R - in the Tumblr entry text field. I have to cut-and-paste them.

• Sun, 14:16: Bell choir is ringing at the city interfaith Thanksgiving service at my church tonight.
• Sun, 19:50: Burdens of the day: achievement unlocked. Except writing and drawing an AKOTAS.
• Sun, 19:53: See, that's why we don't watch SPN while we're eating.
• Sun, 21:01: RT @AfterMASHseasn3: Hot Lips goes on a date with a writer. Alma goes on a date with a fireman.
• Sun, 21:15: #AKOTAS update: sometimes it's update insteada filler just cuz I drew new art. http://t.co/AfqrDpSX #webcomics #kingarthur
• Mon, 05:07: "we should like the very act of liking stuff" http://t.co/Uzx9T8Pp
• Mon, 09:27: I musta been on my feet more'n I realized yesterday, grocery shopping and bell choir, cuz my left knee is still stiff
• Mon, 10:41: I just created Excel formulae to return RGB values for a color up the rainbow for any ratio between 0 and 1.

In closing two recent regular LJ entries (as opposed to entries that are twit mirrors) tagged math, I've promised an entry about Excel spreadsheet formulas correlating the chronologies of other shows to the chronology notation I use for Doctor Who. Even casual readers of my twits or LJ will know that I use this spreadsheet to correlate shows' chronologies for my fanfiction, but all I've ever described in detail here is the way I manipulate the Doctor Who notation for my own entertainment to pass the time. I say "recent", but those journal entries with that promise both date to 2010, and I never delivered on that promise. But now someone's actually asked, so here we go.

First a little review: For the purposes of fanfiction chronology I track the total years since Doctor Who began airing, whether it actually aired any given year or not. The Excel formula for this is:

=(NOW()-(DATE(1963,11,23)+TIME(18,0,0)))/365.25+1

=(NOW()-23338.75)/365.25+1

=($A$1-23338.75)/365.25+1

With the date November 23 1963 at 18:00 in Cell A1, the Cell F4 formula returns the figure 1, signifying the beginning of the first season (or, if you're British, series) of Doctor Who. For the date December 2 2011 22:00 this formula returns the figure (rounded to two decimal places) 49.03, about a week and a half into Doctor Who's forty-ninth year. 4.22 signifies the first regeneration, 16.99 signifies the completion of the Key to Time quest, 33.47 signifies the Master's attempt on the Doctor's TARDIS's Eye of Harmony, 47.99 signifies the Doctor's reunion with Sarah Jane Smith and Jo Grant at his supposed funeral, and so forth.

In my spreadsheet Cell B42, I always have a formula that cycles from the beginning to the present of this notation for the Doctor Who screen history as described in previous entries tagged math, with Excel cell formatting showing two significant digits. All the other cells with formulas tracking other shows' chronologies key on the formula in Cell B42. In my spreadsheet I manage to keep it mostly in a 3x3 cell grid, so some cell formulas track more than one show (sequentially usually, but sometimes concurrently), but I'm not going to combine show formulas for you here.

DOCTOR WHO SERIAL/EPISODE

=IF(OR(B42<1, OR(B42>=48, OR(AND(B42>27, B42<42), AND(B42<47, B42>=43)))),0,1)+MOD(B42,1)*IF(AND(B42>=8, B42<13),5,IF(OR(AND(B42>=5, B42<7), AND(B42>=18, B42<22)),7,IF(OR(AND(B42>=13, B42<18), INT(B42)=22),6,IF(OR(INT(B42)=2, INT(B42)=4),9,IF(INT(B42)=3,10,IF(INT(B42)=1,8,0))))))+MOD(B42,1)*IF(AND(B42<42, B42>=27),0,IF(OR(AND(B42>=23, B42<27), INT(B42)=7),4,IF(INT(B42)=46,5,IF(INT(B42)=42,13,IF(B42>=43,14,0)))))

For instance, for the figure 1.02 in Cell B42, this formula will return the figure 1.24, signifying the near end of Episode 1 of four of An Unearthly Child, the first serial of the first series; about the time the Doctor sets the TARDIS into flight with Barbara and Ian aboard. The figure 42.07 in Cell B42 returns 1.91, signifying most of the way through the first Season 2005 episode Rose, about the time Rose swings out over the vat of Intelligence.

Christmas specials are Episode 0 of the following season. Sarah Jane Adventures: The Wedding of Sarah Jane Smith is counted as Episode 2 of four (where both parts of The End of Time are counted as one episode), between Planet of the Dead and Waters of Mars, of Season 2009. Sarah Jane Adventures: Death of the Doctor is counted as Episode 14 of Season 2010.

DATE

For a very approximate air date from the Doctor Who figure in Cell B42 (the inversion of the Cell F4 formula),

=(B42-1)*365.25+23338.75
apply Excel cell date formatting

STAR TREK

For Star Trek [the original series] stardates (not including the latest movie),

=IF(OR(B42<1.875,B42>29.04),"",IF(B42>26.59,(B42-26.59)/2.45*1069+8454,IF(B42>24.01,(B42-24.01)/2.58*64+8390,IF(B42>21.59,(B42-21.59)/2.42*180+8210,IF(B42>19.59,(B42-19.59)/2*80+8130,IF(B42>=17.04,(B42-17.04)/2.55*518+7612,IF(B42>6.79,(B42-6.79)/10.25*1057+5943,(B42-3.79)/3*4631+1312)))))))
apply number formatting for rounding to one digit

(In regards the "" in the first IF statement - when a cell formula is inactive, I generally prefer a blank text-formatted entry rather than just a zero. Your mileage may vary.)

For Star Trek: The Next Generation stardates (also encompassing Star Trek: Deep Space Nine and Star Trek: Voyager)

=IF(AND(B42>24.84, B42<40),(B42-24.84)*1000+41000,"")
apply number formatting for rounding to one digit

=IF(OR(B42<38.84, B42>42.84),"",(B42-38.84)+2151.29)

If you'd rather do Star Trek: Enterprise by date C.E. in a cell with date formatting:

=IF(OR(B42<38.84, B42>42.84),"",(B42-38.84)*365.25+DATE(2151,4,30))
with suggested date formatting mm/dd/yyyy

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.

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

FOR MOST OTHER SHOWS

With a few exceptions I sample below, most other shows are tracked just by season number. For instance, M*A*S*H:

=IF(AND(B42<22.3, B42>9.8),B42-8.8,"")

(The season count in this formula goes to the middle of thirteen, instead of only through eleven, because I include AfterM*A*S*H.) (Also, M*A*S*H could have an alternative formula in date format like Enterprise, with the dates of the Korean War, if you wanted; but I don't do it that way.)

Buffy the Vampire Slayer can't be expressed in a formula that simple because it was a midseason starter and its first season was short.

=IF(AND(B42<40.8,B42>=34.8),B42-32.8,IF(AND(B42<34.8,B42>=34.12),(B42-34.12)/0.68+1,""))

(Or, adjust the first argument of the first AND statement to B42<41.8 to include the last season of the concurrently running spinoff Angel.)

OTHER SPECIAL CASES
HITCHHIKER'S GUIDE TO THE GALAXY

My favorite version of Hitchhiker's is the tv version, it being the first I encountered, and I being a visual person. The six episodes aired over a five-week period in 1981. Now, I also wanted my formula to accurately represent that these characters continue to appear in cartoons set after the adventures chonicled in the program - but, in keeping with the spirit of the sourcework, only improbably crossing over with other characters. So that cell displays episode numbers during the run of the program, and for 11% of the time afterwards it displays the number 42.

=IF(AND((B42>18.24), MOD(B42,0.000001)>=0.00000089),42,IF(AND(B42>=18.12, B42<=18.23999),(B42-18.12)/0.02+1,""))

THE PRISONER

By episode number.

=IF(AND(B42>=4.78, B42<=5.22),(B42-4.78)/0.44*16+1,"")

STAR WARS

By episode number.

=IF(AND(B42<=45.49, B42>=36.5),(B42-36.5)/3+1,IF(AND(B42<=23.49, B42>=14.5),(B42-14.5)/3+4,""))

Or, including the Clone Wars tv show; bearing in mind a) those are set between Episode Two and Episode Three, and b) I don't know how long the series will last (though I think I read recently it was only going 100 episodes?),

=IF(B42>=46,(B42-46)/(B42-45)+2,IF(AND(B42<=45.49, B42>=36.5),(B42-36.5)/3+1,IF(AND(B42<=23.49, B42>=14.5),(B42-14.5)/3+4,"")))

BABYLON 5

By year set.

=IF(AND(B42>=30.8, B42<35.794),INT(B42-30.794+2258),"")

INDIANA JONES

The first three movies and The Young Indiana Jones Chronicles (I do recognize the fourth movie, but by then that cell has something else in it so I've never extended the formula that far) (updated for the fourth movie). By year set, which means it initially goes backwards.

=IF(B42>=45.55,TRUNC(1957+B42-45.55),IF(AND(B42<34.55, B42>=28.5),TRUNC(1938+B42-IF(AND(AND(B42<34.55, B42>=28.5), MOD(INT(B42*100),3)=0),58.5,48.5)),IF(AND(B42<34.55, B42>=21.5),TRUNC(1935+3*(B42-21.5)/4),IF(AND(B42<34.55, B42>=18.5),TRUNC(1936-(B42-18.5)/3),""))))

PLANET OF THE APES FILMS

By film number (not including revivals), with the tv show as film six.

=IF(AND(B42<=12.3, B42>=5.1),IF(B42>=11.8,6+(B42-11.8)*1.98,IF(B42>=10.1,(B42-10.1)/1.7+5,IF(B42>=8.1,3+B42-8.1,IF(B42>7.497,2,(B42-5.1)/2.397+1)))),"")

CEREBUS THE AARDVARK

By issue number.

=IF(AND(B42>=15.06, B42<=41.145),INT(IF(B42<16.06,(B42-15.06)*6+1,(B42-16.06)/25*293+7)),"")

That should be information enough for anyone who wants to use these formulas, but also wants one for a show that's not listed here, to work out how to write their own. Though of course I'm willing to answer questions.

Meanwhile, for anyone who is interested in the time tracking I do with the Doctor Who notation, as described in earlier entries tagged math: Currently my default is to divide a month into eleven Doctor incarnations. The formula in Cell B42 is the one previously described for dividing time into eleven equal parts, and prorating each part to each incarnation's screen history. This version assumes Cell A42 has a value between 0 and 1, Cell F4 contains the Doctor Who current year count as expressed in this essay's first formula, and an entry in Cell E5 that is the current number of incarnations.

=IF(A42>1,$F$4,IF(A42>10/$E$5,(A42-10/$E$5)*$E$5*($F$4-47)+47,0))+IF(A42>10/$E$5,0,IF(A42>9/$E$5,(A42-9/$E$5)*$E$5*4+43,IF(A42>8/$E$5,(A42-8/$E$5)*$E$5+42,IF(A42>7/$E$5,(A42-7/$E$5)*$E$5*8.53+33.47,IF(A42>6.75/$E$5,(A42-6/$E$5)*$E$5*25.88+7.59,IF(A42>6/$E$5,(A42-6/$E$5)*$E$5*4+24,IF(A42>5/$E$5,(A42-5/$E$5)*$E$5*15/7+153/7,0)))))))+IF(A42>5/$E$5,0,IF(A42>4/$E$5,(A42-4/$E$5)*$E$5*20/7+19,IF(A42>3/$E$5,(A42-3/$E$5)*$E$5*7+12,IF(A42>2/$E$5,(A42-2/$E$5)*$E$5*5+7,IF(A42>1/$E$5,(A42-1/$E$5)*$E$5*25/9+38/9,A42*$E$5*38/9)))))

I have a macro with a generic formula for Cell A42 for waking hours throughout the current month (where waking hours are tracked in Cell C2 as previously described), but copy-pasting that formula here breaks my HTML in a way that no other of these formulas does. So here's a quick and dirty formula specific to this month.

=($C$2-DATE(2011,12,1))/31
If you don't have a waking hours formula in Cell C2, for $C$2 substitute $A$1 or NOW().

So at December 2 22:00 (with waking hours of 07:00-23:00) the Doctor Who notation returned is 2.90, signifying about 13% of the way through the Season 1965 finale The Time Meddler, with an approximate airdate of 10/18/65, with an approximate stardate of -57.6*, with Get Smart and Hogan's Heroes about ten percent through their first seasons, and Bewitched and Gilligan's Island about ten percent through their second seasons.

* Yes, there are negative stardates in my fanfiction.

• Wed, 13:53: Whoa hey, Deodato recorded Moonlight Serenade. The things you learn from YouTube.
• Wed, 13:53: @stoplookingup Thanks. I hope your trials start going smoother too.
• Wed, 13:54: @stoplookingup I think of you whenever shots of New Jersey come on CNN.
• Wed, 14:21: @dantheshive I wonder that about all spam. Do people do it because it works, or because it's easy? IS it easy?
• Wed, 15:39: Hero of Three Faces update: a less uncommon name than you think. http://t.co/OoO91Kv #dcu #bsg
• Wed, 17:10: Home from last chemo. Doctor Who reruns on BBCAmerica. The Doctor and Rose. The best medicine @horseyparalegal could ask for.
• Wed, 18:29: @RedneckGaijin When my argument is Lucas has every right to do as he's doing, how is that it's done to only some of his works a rebuttal?
• Wed, 18:41: @RedneckGaijin You may be correct about Lucas' motivation. My position is, he has the right regardless of motivation. Legally and morally.
• Wed, 18:48: Lucas has the right to do what he likes with his story, and suffer the consequences in the market. Same as any storyteller.
• Wed, 19:02: @fourzoas It took me a moment, but I laughed aloud at the trig joke.
• Wed, 20:09: #AKOTAS sketch: I was going to draw the ship, then I remembered it cloaks. http://t.co/HdAeXoQ #webcomics #kingarthur
• Wed, 20:19: The Greek Tithonus. RT @faxpaladin @AKOTAS Who's the Captain Jack surrogate?
• Thu, 09:58: Hero of Three Faces update: my pop is better than your pop. http://t.co/WTKqgll #dcu
• Thu, 11:40: Forget Catwoman. Have you seen Anne Hathaway in glasses?
• Thu, 12:49: @spastasmagoria I'm drawing a Superman/Doctor Who crossover you inspired.

Tweets copied by twittinesis.com

Tweets copied by twittinesis.com

The last time I wrote in this journal an Excel formula for tracking time against the broadcast history of Doctor Who through the days, it was a season an hour: =MOD(NOW()*24,34)

I'd previously also reported tracking by a Doctor an hour: =MOD(NOW()*24,11)+1

or by a Doctor a day, for which the formula would be: =MOD(NOW(),11)+1

and had implied tracking by a calendar year an hour: =MOD(NOW()*24,48)

Since last report, I've been switching between tracking formulas fairly often, unable to stay satisfied with any for long. Then, the past few days (perhaps as a function of being at home relatively idle in anticipation of starting a new job next week), I've stopped even trying to stick with the same formula for more than a few hours at a stretch. I've been advancing as quickly, or as slowly, or for as long in a loop describing a season or an incarnation, as whim dictates.

The formula I'm using at the moment looks like this:

=RAND()+INT(MOD((NOW()-40326-19.5/24)*24*4,11))+1

This formula advances through the Doctor's eleven incarnations (MOD(...,11)+1) one every quarter (*4) of an hour(*24), starting today at 7:30 pm (NOW()-40326-19.5/24); except, instead of proceeding linearly through each incarnation's time, it generates random (RAND()+INT(...)) points of that incarnation's time during that incarnation's quarter hour. Just the sort of thing that's all very unintelligible, even to others who also use Excel recreationally, unless you wrote it yourself, I'm sure. And you also may be wondering, "What's the point of generating random values between 4 and 5, when you don't know which points of the Doctor's fourth life those values relate to?" So unless you use Excel very recreationally, you may not want to go on to the rest of this.

Because now's when I point out that I only said the formula I'm using right now "looks like" the one I set off above. This is the one I'm really using:

=(RAND()+INT(MOD(($A$1-40326-19.5/24)*24*4,11)))/11

... where cell A1 contains the formula =NOW(). The above formula, like the formula =RAND() alone, generates values between 0 and 1; but these are values that are proportional to the previous formula's values between 1 and 12. Because the second formula wasn't built to stand on its own. It was built to be read by a third formula, which as written here assumes that the second formula is in cell A42 (cell F4 is also referenced, but the formula there will be described below):

=IF(A42>10/11,(A42-10/11)*11*($F$4-47)+47,0)+IF(A42>10/11,0,IF(A42>9/11,(A42-9/11)*11*4+43,IF(A42>8/11,(A42-8/11)*11+42,IF(A42>7/11,(A42-7/11)*11*8.53+33.47,IF(A42>6.75/11,(A42-6/11)*11*25.88+7.59,IF(A42>6/11,(A42-6/11)*11*4+24,IF(A42>5/11,(A42-5/11)*11*15/7+153/7,0)))))))+IF(A42>5/11,0,IF(A42>4/11,(A42-4/11)*11*20/7+19,IF(A42>3/11,(A42-3/11)*11*7+12,IF(A42>2/11,(A42-2/11)*11*5+7,IF(A42>1/11,(A42-1/11)*11*25/9+38/9,A42*11*38/9)))))

The third formula takes a value between 0 and 1, breaks it down into eleven equal segments, and breaks each of those segments into segments equal to the relevant Doctor incarnation's screen history - in calendar years as opposed to in seasons; where season counts only actual screen time produced but calendar years counts time since series initial broadcast - essentially what the season count would be if Doctor Who had aired continuously. An input into the third formula of 0 through 1/11 will return a value between 0 and 4.2222222, because the Doctor's first incarnation's travels go from when he left Gallifrey with Susan before the programme started until the end of the second of nine serials of Series 4 in 1966. An input of 6/11 through 7/11 will return a value between 24 and 33.47, because the Doctor's seventh incarnation's travels go from the regeneration scene at the beginning of Season 24 through three seasons and through six-plus years of broadcast hiatus until the regeneration scene in the May 14, 1996 movie broadcast.

You may ask, what makes me say that 33.47 equals May 1, 1996? This fourth formula, for determining approximate airdate from the third formula (assumes the third formula is in cell B42):

=(B42-1)*365.25+23338.75

... which from the value 1 returns (when date/time formatted in Excel) the value November 23, 1963, 18:00; from the value 33.4739 returns the value May 14, 1996, 20:12; from the value 47.51119 returns the value May 28, 2010, 23:05. Cell F4 has the inverse formula =($A$1-23338.75)/365.25+1, where cell A1, you already know, has the formula for the current time; so the cell F4 formula returns the current time calendar year value, tonight 47.51.

You may ask, remembering that I usually mention my spreadsheet formulas in the context of tracking chronologies for my crossovers, "If you've got a formula for calculating approximate airdate from this calendar year value, you must also have formulas for calculating things like segmenting seasons into episodes, and calculating the stardates in Star Trek's broadcast history relative to Doctor Who's broadcast history."

(I don't know whether there's anyone asking that, or even reading this far into this entry. No one's commented directly on the math of these journal entries tagged math, and I suspect I'm preaching to the pulpit. But anyway:)

That's right. But that can wait for another post. But if you divided this year into eleven equal segments, midnight tonight would be March 18, 1983, approximately toward the end of episode one of Mawdryn Undead, and sometime between The Wrath of Khan and The Search for Spock.