Date Time increment with additional condition of skipping weekends

[shriil]

Hi

I have a date column where the fields are incremented by 10 minutes
starting from 9:00 Hrs to 15:30 Hrs. The column dates are indicated as
below

A
sep -23 9:00
9:10
9:20
9:30
9:40
......
.....
15:20
15:30
sep-24 9:00
9:10
....

The second condition that needs to be sufficed is if the next date
falls on a Saturday, then the date shall jump to the next Monday 9:00
and then continue to be incremented by 10 minutes. This would mean
that after sep -24 : 15:30 the next date shall have to be sep-27:
9:00, as Sep -24 is a Friday and the next date becomes Saturday, so it
should jump to sep-27 monday.

I have more or less made a formula for say, if sep -24 : 15:30 is in
Cell (A17) then the formula in Column (A18) shall be

=IF(TIME(HOUR(A17),MINUTE(A17),SECOND(A17))<>TIME(15,30,0),A17+TIME(0,10,0),IF(WEEKDAY(A17+TIME(17,30,0))=7,A17+TIME(17,30,0)+TIME(23,0,0)+TIME(1,0,0)+TIME(23,0,0)+TIME(1,0,0),A17+TIME(17,30,0)))

The formula is then dragged down for all the cells.

The formula is working well but somehow its doesnt seem to a very
smart formula and is cumbersome.

Can the above logic be inducted in a better formula?

Thanks

 

[Hans Terkelsen]

Hi

I have a date column where the fields are incremented by 10 minutes
starting from 9:00 Hrs to 15:30 Hrs. The column dates are indicated as
below

A
sep -23 9:00
9:10
9:20
9:30
9:40
......
.....
15:20
15:30
sep-24 9:00
9:10
....

The second condition that needs to be sufficed is if the next date
falls on a Saturday, then the date shall jump to the next Monday 9:00
and then continue to be incremented by 10 minutes. This would mean
that after sep -24 : 15:30 the next date shall have to be sep-27:
9:00, as Sep -24 is a Friday and the next date becomes Saturday, so it
should jump to sep-27 monday.

I have more or less made a formula for say, if sep -24 : 15:30 is in
Cell (A17) then the formula in Column (A18) shall be

=IF(TIME(HOUR(A17),MINUTE(A17),SECOND(A17))<>TIME(15,30,0),A17+TIME(0,10,0),IF(WEEKDAY(A17+TIME(17,30,0))=7,A17+TIME(17,30,0)+TIME(23,0,0)+TIME(1,0,0)+TIME(23,0,0)+TIME(1,0,0),A17+TIME(17,30,0)))

The formula is then dragged down for all the cells.

The formula is working well but somehow its doesnt seem to a very
smart formula and is cumbersome.

Can the above logic be inducted in a better formula?

Thanks

Hi S.

The formula is working well, so a change would just be cosmetic.
Same logic:
=A17+"0:10"+(HOUR(A17)=15)*(MINUTE(A17)=30)*("17:20"+2*(WEEKDAY(A17)=6))

Binary errors accumulate with formulas like that, if there are many additions of 10 min.
But fortunately they are positive, and amount to a second only after 1500 years

Hans T.

 

[joeu2004]

=A17+"0:10"+(HOUR(A17)=15)*(MINUTE(A17)=30)
*("17:20"+2*(WEEKDAY(A17)=6))

Binary errors accumulate with formulas like that, if there are many
additions of 10 min.
But fortunately they are positive, and amount to a second only after
1500 years

I get a 1-second error after "just" 161y 0m 4d 5h 20m (9/27/2171
14:20) -- 1,680,272 iterations after 9/23/2010 9:00:00.

I am careful to do arithmetic as Excel does, namely: converting to 64-
bit floating-point after each operation.

But even if we utilize the 80-bit floating-point of a Pentium-
compatible FPU, which VBA does, I get a 1-second error after 166y 5m
9d 3h 30m (3/4/2177 12:30) -- 1,736,941 iterations.

Moreover, the differences are __not__ always "positive", if I
understand what you mean by that. The off-by-one-second times noted
above are 14:19:59 instead of 14:20:00 and 12:29:59 instead of
12:30:00 respectively.

Of course, that is academic.

For the typical user, the more significant issue is: after adding
0:10:00 just 5 times (9/23/2010 9:50), the binary representation
begins to differ from the equivalent contant. Consequently, some
exact comparisons will fail; for example, VLOOKUP(...,0).

And after adding 0:10:00 10 times (9/23/2010 10:40), even simple
comparisons (A1=A2) begin to fail intermittently with increasing
frequency.

A more reliable formula would be:

=--TEXT(IF((HOUR(B1)=15)*(MINUTE(B1)=30),
B1+"17:30"+2*(WEEKDAY(B1,2)=5), B1+"0:10"),
"m/d/yyyy h:m")

That should ensure that the result always matches the equivalent
constant.

Also, the IF() expression may be more efficient insofar as 2*WEEKDAY
is computed only 20% of the time.

Arguably, if the Shriil is never interested in matching with
constants, the addition overhead of the --TEXT(...) is not worth the
trouble.

But it is something to keep in mind when the paradigm (iterative
addition of time) is used in other contexts.

 

[joeu2004]

=--TEXT(IF((HOUR(B1)=15)*(MINUTE(B1)=30),
B1+"17:30"+2*(WEEKDAY(B1,2)=5), B1+"0:10"),
"m/d/yyyy h:m") [....]
Also, the IF() expression may be more efficient insofar as 2*WEEKDAY
is computed only 20% of the time.

Ah, only 2.5% of the time.

 

[joeu2004]

I get a 1-second error after "just" 161y 0m 4d 5h 20m (9/27/2171
14:20) -- 1,680,272 iterations after 9/23/2010 9:00:00.

That is with Hans's algorithm to meet Shriil's requirements.

But if we simply add 0:10 to 9/23/2010 9:00:00, I get a 1-second
after "just" 94y 3m 17d 17h 0m (1/10/2105 2:00) -- 4,959,423
iterations.

 

[Hans Terkelsen]

I get a 1-second error after "just" 161y 0m 4d 5h 20m (9/27/2171
14:20) -- 1,680,272 iterations after 9/23/2010 9:00:00.

That is with Hans's algorithm to meet Shriil's requirements.

But if we simply add 0:10 to 9/23/2010 9:00:00, I get a 1-second
after "just" 94y 3m 17d 17h 0m (1/10/2105 2:00) -- 4,959,423
iterations.

Hi Joeu!

You are probably right about the 1 second error years.
I just did a calculation in the head.
Or maybe there are version differences, mine is XL03.

By the way 10 minutes, 1/144, Excels nearest number is just a little too small.
But over long additions the result becomes too large for a while!

The formulas could be tweaked to avoid most of the errors,
In this case it seemed unnecessary though.

Hans T.

 

[alanglloyd]

Hi

I have a date column where the fields are incremented by 10 minutes
starting from 9:00 Hrs to 15:30 Hrs. The column dates are indicated as
below

A
sep -23 9:00
            9:10
            9:20
            9:30
            9:40
            ......
            .....
            15:20
            15:30
sep-24   9:00
             9:10
              ....

The second condition that needs to be sufficed is if the next date
falls on a Saturday, then the date shall jump to the next Monday 9:00
and then continue to be incremented by 10 minutes. This would mean
that after sep -24 : 15:30 the next date shall have to be sep-27:
9:00, as Sep -24 is a Friday and the next date becomes Saturday, so it
should jump to sep-27 monday.

I have more or less made a formula for say, if sep -24 : 15:30 is in
Cell (A17) then the formula in Column (A18) shall be

=IF(TIME(HOUR(A17),MINUTE(A17),SECOND(A17))<>TIME(15,30,0),A17+TIME(0,10,0)­,IF(WEEKDAY(A17+TIME(17,30,0))=7,A17+TIME(17,30,0)+TIME(23,0,0)+TIME(1,0,0)­+TIME(23,0,0)+TIME(1,0,0),A17+TIME(17,30,0)))

The formula is then dragged down for all the cells.

The formula is working well but somehow its doesnt seem to a very
smart formula and is cumbersome.

Can the above logic be inducted in a better formula?

Yes.

Consider first putting any constants into its own cell. This reduces
the active cells function length, & also makes changes easy (think
what you would have to do if the the day ended with 15:20 instead of
15:30).

These constants could be put into hidden rows/columns or put outside
the Print Area. This also would allow you to put comments against them
("Magic Numbers" ie numbers which have no inherent meaning) are not
understood after 6 months (the Six Month Rule).

So put :

Column A B
Row 1 Start Date 23/9/2010
2 Day End =Time(9.0,0)
3 Day End = Time(15,30,0)
4 Increment = Time(0,10,0)

The date-time values in Excel are formated as an integer for the days
and a decimal fraction for the time. So INT(<day-value>) returns the
day. There is no function FRAC() to return the time (at least in my
Excel) so one must use <day-value> - INT(<day-value).

So to the active cells :

Column B
Row 6 & on =IF(INT(B6)=INT(B5),0,B6)

Column C
Row 6 =B1+B2

Column C
Row 7 & on =IF(B6-INT(B6)>$B$3,INT(B6)+IF(WEEKDAY(B6,1)=6,3,1)+$B$2,B6+
$B$4)

That is :

If (PreviousCell date-time - PreviousCell date) > Day End then
PreviousCell + If(PreviousCell is Friday, 3 else 1) + Increment
else
PreviousCell + Increment.

Then format active column A as : mmm dd;"";""
Format active column B as h:mm

Alan Lloyd

 

[Hans Terkelsen]

A bit shorter:

A18:

=A17+10/1440+AND(HOUR(A17)=15,MINUTE(A17)=30)*(1040/1440+2*(WEEKDAY(A17)=6))

Algorithm:

1. Add 10 minutes to previous value in cell above

2. If, in previous cell, HOUR = 15 and MINUTE = 30 then Add 17 hrs
20 minutes

3. If, "2" is true and also WEEKDAY=6 (Friday), add an additional 2
days.

Repeated 1,048,575 times (the maximum number of rows in a column for
Excel 2007), there is an error of 143 msec in the time computation.

If that is unsatisfactory, you could ROUND the computation to the
nearest 10 minutes:

=ROUND((A17+10/1440+AND(HOUR(A17)=15,MINUTE(A17)=30)*(1040/1440+2*(WEEKDAY(A17)=6)))*144,0)/144

Hi Ron!



143 msec of error after what I think is about 20 years, pretty good!
The error seems to go up and down.

Thanks, Hans T.

 

[Ken Mintz]

=A17+10/1440+AND(HOUR(A17)=15,MINUTE(A17)=30)
*(1040/1440+2*(WEEKDAY(A17)=6)) [....]
Repeated 1,048,575 times (the maximum number of rows in
a column for Excel 2007), there is an error of 143 msec in the
time computation.

[....]
143 msec of error after what I think is about 20 years, pretty good!
The error seems to go up and down.

The numerical behavior should be no different from the formula Hans
posted 12 hours earlier, including the 143-msec computational
difference, which I did not vet.

The internal representations of 10/1440 and 1040/1440 are identical to
--"0:10" and --"17:20" in Hans's formula.

So they are nearly the same implementation of the same algorithm. The
difference is: Ron used numeric expressions instead of time
constants, and he used AND instead of multiplication.

 

[joeu2004]

You are probably right about the 1 second error years.
I just did a calculation in the head.
Or maybe there are version differences, mine is XL03.

I am using Excel 2003. But I am quite sure that the version of Excel
makes no difference, at least when seconds are zero. [1]

By the way 10 minutes, 1/144, Excels nearest number is just a little too small.

I thought that might be the mistake you are making.

Yes, the time 0:10:00, which is the same as 10/1440, is represented
internally exactly as
0.00694444444444444,40589503386718206456862390041351318359375, whereas
the mathematically correct value would repeat 4 infinitely. So there
is a numerical "error" of less than -3.85494E-19.

However, you are forgetting that Shriil included a date; we are adding
0:10:00 to 9/23/2010 9:00 initially. As it happens, that date/time is
represented exactly, namely 40444.375. But since that date consumes
14 of the 53 bits (including the assumed bit to the left of the radix
point), there are fewer bits to represent 0:10:00.

In fact, 9/23/2010 0:10 is represented internally exactly as
40444.0069444444,452528841793537139892578125. So when we add 0:10:00
to dates near 9/23/2010, we are effectively adding
0.0069444444,452528841793537139892578125. Note that that is slightly
larger, not smaller, than the mathematically correct decimal fraction.

Of course, the vagaries of computer floating-point arithmetic are not
quite that simple. But the fact is: if we add 0:10:00 to 9/23/2010
9:00, the exact internal representation is
40444.3819444444,452528841793537139892578125 -- larger than the
mathematically correct decimal fraction.

However, what really matters is how that compares with the internal
representation of the constant 9/23/2010 9:10. In that case, the two
internal representations are exactly the same. So there is no
computational "error" in that case. It is as good as it gets within
the limits of 64-bit floating-point representation.

But over long additions the result becomes too large for a while!

The vagaries of floating-point arithmetic and floating-point
conversion are too difficult to make predictions. The fact remains
that when add 0:10:00 iteratively starting with 9/23/2010 9:00
(straight-forwardly, not following Shriil's requirements), the first
off-by-one-second time is less than the intended time, namely
01/10/2105 05:39:59. [2]

In fact, compared with the equivalent constant, the internal
representation of the result of adding 0:10:00 is:

* too high starting with 09/23/2010 09:50:00
* too low starting with 01/05/2088 11:10:00
* too high starting with 01/10/2105 05:39:59
* too low starting with 01/15/2122 00:49:59
* too high starting with 01/19/2139 19:19:58
* too low starting with 01/24/2156 14:19:58
* too high starting with 01/28/2173 08:59:57
* too low starting with 02/02/2190 03:59:57
* too high starting with 02/07/2207 22:29:56
* too low starting with 02/12/2224 17:39:56
....etc....

The formulas could be tweaked to avoid most of the errors,
In this case it seemed unnecessary though.

How do you know? I don't see anything in Shriil's OP that explains
his use of the column of times. If they are part of a lookup table,
his/her reliance on the "inaccurate" results (i.e. different from the
equivalent constants) might result in misbehavior of his spreadsheet.
Likewise if he/she uses the "inaccurate" results as lookup values.

It is simply my style to alert the clueless user to the problem and
the more-complicated solution, and let the user decide what is and is
not necessary for his purposes.


-----
Endnotes

[1] Some time ago, I discovered that in Excel 2003, TEXT(...,"h:m:ss")
does not always result in the same binary representation as the
equivalent constant. Ron Rosenfeld found that that was corrected in
Excel 2007. But I believe that is only for times with some non-zero
amounts of seconds.

[2] I had previously said the first off-by-one-second is 1/10/2105
2:00. First, that was the intended time; the actual time was
1:59:59. Second, I had used the wrong starting time, 9/24/2010 15:30
as I recall.

 

[joeu2004]

Errata....

 As it happens, that date/time is represented exactly, namely
40444.375.  But since that date consumes 14 of the 53 bits
(including the assumed bit to the left of the radix point)

Of course, 40444 consumes 16 bits. I had looked at the 64-bit
floating-point value in hex and miscounted because the least-
significant two bits are zero.

 

[shriil]

A bit shorter:

A18:  

=A17+10/1440+AND(HOUR(A17)=15,MINUTE(A17)=30)*(1040/1440+2*(WEEKDAY(A17)=6)­)

Algorithm:

1.  Add 10 minutes to previous value in cell above

2.  If, in previous cell, HOUR = 15  and MINUTE = 30 then Add  17 hrs
20 minutes

3.  If,  "2" is true and also WEEKDAY=6 (Friday), add an additional2
days.

Repeated 1,048,575 times (the maximum number of rows in a column for
Excel 2007), there is an error of 143 msec in the time computation.

If that is unsatisfactory, you could ROUND the computation to the
nearest 10 minutes:

=ROUND((A17+10/1440+AND(HOUR(A17)=15,MINUTE(A17)=30)*(1040/1440+2*(WEEKDAY(­A17)=6)))*144,0)/144- Hide quoted text -

- Show quoted text -

Thanks a lot guys. I guess Ron's iteration yields the minimum error!

 

[joeu2004]

=A17+"0:10"+(HOUR(A17)=15)*(MINUTE(A17)=30)
*("17:20"+2*(WEEKDAY(A17)=6))

=A17+10/1440+AND(HOUR(A17)=15,MINUTE(A17)=30)
*(1040/1440+2*(WEEKDAY(A17)=6))

I guess Ron's iteration yields the minimum error!

They should have identical results, since 10/1440 and 1040/1440 have
the same internal representation as "0:10" and "17:20".

At least, that is the case with Excel 2003. You can confirm that in
your version of Excel by entering the =10/1440 and =1040/1440 into A1
and A2, and enter =--"0:10" and =--"17:20" into B1 and B2. Then enter
=(A1-B1) and =(A2-B2) into C1 and C2. Note that the redundant
parenthese are necessary. The result in C1 and C2 should be 0.00E+0
when the cell is formatted Scientific with 2 decimal places.

 

[shriil]

That's probably true if you are referring to my second version using
the ROUND function.  But, given your resolution of 10 minutes, the
error without rounding is not significant.

Yes Ron. thats right. The error without rounding is quite
insignificant. But thanks to all of you for delving into the problem
so deeply. The 'error' part that may come after some amount of
iterations actually decides which formula would be the smartest