scarfman | god emperor of fandom math

I alluded in a twitpost recently to the "waking hours formula" in the Excel spreadsheet with the formulas I discuss in posts tagged "math". But first, a digression.

In the last major math post, I shared a formula for converting the result of another cell into the calendar years of the Doctor's screen history with the years each incarnation prorated into eleven equal segments (where "calendar years" is different from "seasons" in that it counts all years since initial airing in 1963). Proration into seasons is a little easier.

First, to review: I divide the existing screen history of Doctor Who (including the season [or, if you're British, series] now beginning primary shooting for airing next year, since any season counts about which I have sufficient information to draw cartoons) into thirty-six seasons: a zeroth season for the Doctor's travels with his granddaughter Susan alone, one each for the twenty-six seasons aired on the BBC 1963-1989, one for Dimensions in Time, one for the 1996 movie, and one each for the seven seasons 2005-2011 inclusive (counting the 2009 "specials" separately). To track time a season an hour, the basic formula is:

=MOD(NOW()*24,36)

But, just like last entry's formula for prorating incarnations to calendar years, I have a formula for prorating seasons, when prorating is necessary, to calendar years. This formula assumes

a formula in cell A42 with a value between 0 and 1
as discussed last entry, a formula in cell F4 with the current time calendar year value (=(A1-23338.75)/365.25+1)
a formula in cell E4 with the current season count, presently 36

=IF(A42>=($E$4-2)/$E$4,IF($F$4>48,(A42-($E$4-1)/$E$4)*$E$4*MOD($F$4,1)+INT($F$4),(A42-($E$4-2)/$E$4)*$E$4/2*MOD($F$4,1)+INT($F$4)),IF(A42>29/$E$4,(A42-29/$E$4)*$E$4+42,IF(A42>28/$E$4,(A42-28/$E$4)*$E$4*8.53+33.47,IF(A42>27/$E$4,(A42-27/$E$4)/(1/$E$4)*6.47+27,A42*$E$4))))

(This formula fudges the last two "seasons" somewhat, in that it shall divide the incomplete calender "year" since 11/23/09 into two "seasons", until 11/23/10.)

I digress because most recently I've been tracking time a season a day. The formula for that, in cell A42 for the above formula to read, would be, with (as discussed before) cell A1 containing the formula =NOW():

=MOD($A$1,36)/36

=MOD($A$1,$E$4)/$E$4

But I find that unsatisactory because that way I must miss a third of each season to the human necessity for sleep. So I instead use this formula:

=MOD($C$2,$E$4)/$E$4

where cell C2 contains a formula which progresses only during hours I've designated my usual waking hours. On my current work schedule, my weekday waking hours tend to be 05:00 till 22:00. So the basic waking hours formula in my cell C2, where in A1 is =NOW() and in B1 is =MOD(A1,1) :

=INT(A1)+IF(B1<TIME(5,0,0),0,IF(B1<TIME(22,0,0),(B1-TIME(4,0,0))/TIME(17,0,0),1)

But, as I say, that's only a basic waking hours formula. As the years went by I came to dislike it, first for not being versatile enough. I don't spend the same hours awake every single day, if for no other reason than I sleep later and stay up later on weekends. So I set aside cell C1 for cell C2 to read waking hours from. In cell C1, the numeral(s) to the left of the decimal point became the start time in hours, and the numerals to the right became the end time; the default therefore being =5.22. So, where cell H1 contains a formula for calculating elapsed waking hours time in hours =MOD(C1,1)*100-INT(C1) :

=INT(A1)+IF(B1<TIME(INT(C1),0,0),0,IF(B1<TIME(MOD(C1,1)*100,0,0),(B1-TIME(INT(C1),0,0))/TIME(H1,0,0),1)

But that still wasn't versatile enough, because it constrained waking hour endpoints to increments of an hour - it couldn't handle fractions of an hour. So, where

cell B2 contains formula =B1*24
cell C1 contains the waking start time in hours times a thousand and the end time in hours divided by a hundred, e.g. =5000.22
cell H1 contains the elapsed time of waking hours in hours =MOD(C1,1)*100-INT(C1)/1000

=INT(A1)+IF(B2<INT(C1)/1000,0,IF(B2<MOD(C1,1)*100,(B2-INT(C1)/1000)/H1,1)

So, on Saturday cell C1 can be temporarily changed when I get up to, say, =9750.24; and where a thousandth of an hour is about four seconds, there's sufficient versatility for my purposes. So that's what I twitted about so proudly the other day.

But sometimes I'm up just a little late, or just a little early; or in the middle of the night unexpectedly, say, with a coughing fit. When I'd look at a waking hours timetracking that was frozen at the turnover of a season (or an incarnation, or a calendar year) instead of progressing at least slowly, it didn't serve the purpose it was designed for. It doesn't pass my time if the Doctor's time is suspended.

Therefore I spent a Saturday afternoon's brainpower on adapting the waking hours formula so that, between midnight and .01 of elapsed waking hours past the start of waking hours, the formula progresses through the first .01 of the season; and at the end of the day the last .01 of the season is similarly elongated.

=INT(A1)+IF(B2<INT(C1)/1000+0.01*H1,0.01*(B2)/(INT(C1)/1000+0.01*H1),IF(B2<=INT(C1)/1000+H1-0.01*H1,(B2-INT(C1)/1000)/H1,0.99+0.01*(B2-(MOD(C1,1)*100-0.01*H1))/(0.01*H1+(24-MOD(C1,1)*100))))

In the last math post I promised a post describing formulas for tracking other stories' air histories than Doctor Who. This isn't it, but that post will probably be coming soon, because I started composing it in my head while I was wrapping this one up.