Incrementing dates

[John Harrison]

I want to do something that I feel ought to be easy but I can't work out a
way to combine Firewokz' functions to achieve it.

I have a spreadsheet in which I enter values every Thursday. The rows are
numbered 1 to 52 (or 53) but I would like a column to show the actual
date, which would make it easier when referring back.

The obvious way would be to enter the date in week zero for the relevant
year (eg 28 December 2023 for this year) and have a formula in each row
that 'adds a week' to the one above.

I've looked at the time/date functions but so far failed to work out how
to combine them to get the desired result. Any suggestions?

--
John Harrison
Website http://jaharrison.me.uk
Using 4té and ARMX6, both running RISC OS

[Chris Hughes]

In message <5b7b9e9...@jaharrison.me.uk>

John Harrison <jo...@jaharrison.me.uk> wrote:

> I want to do something that I feel ought to be easy but I can't work out a
> way to combine Firewokz' functions to achieve it.

> I have a spreadsheet in which I enter values every Thursday. The rows are
> numbered 1 to 52 (or 53) but I would like a column to show the actual
> date, which would make it easier when referring back.

> The obvious way would be to enter the date in week zero for the relevant
> year (eg 28 December 2023 for this year) and have a formula in each row
> that 'adds a week' to the one above.

> I've looked at the time/date functions but so far failed to work out how
> to combine them to get the desired result. Any suggestions?

I do something similar, but daily

Change column style to the date format you want.

I put the actual date in the first row, then in the second row I would put
something like a1+1 in your case you would want a1+7 then replicate that
field downwards.

I have just tested it and it works.

--
Chris Hughes

[Martin]

In article <a2fca57b5b.chris@mytardis>,

Recently I wanted a column with the last date in a month, incremented
each row, which was not immediately obvious.
After some investigation I now use 'a1+monthdays(a1+1)'
Works a treat!

[John Harrison]

In article <a2fca57b5b.chris@mytardis>,
Chris Hughes <li...@noonehere.co.uk> wrote:

> I put the actual date in the first row, then in the second row I would
> put something like a1+1 in your case you would want a1+7 then
> replicate that field downwards.

Chris, thanks. Someone else pointed that out off list.

It's similar to what I did before moving it from Eureka
(except there I added 7 times a master time increment derived by
subtracting two adjacent days).

What surprises me is that I've tried so many things with Fireworkz that I
didn't manage to work that out.

What I would really like to do is to enter the year in the master cell
(rather than the date of the last Thursday in the preceding year). But
that would require a function that could input a week number, whereas
there only seem to be functions that output week number.

[Harriet Bazley]

On 5 Jul 2024 as I do recall,

John Harrison wrote:

> What I would really like to do is to enter the year in the master cell
> (rather than the date of the last Thursday in the preceding year). But
> that would require a function that could input a week number, whereas
> there only seem to be functions that output week number.
>

Cribbed from an Excel forum:

date(YYY,MMM,1+7*NNN)-weekday(date(YYY,MMM,8-5)) will give the
Nth Thursday in the month MMM of the year YYY.

https://www.excelfox.com/forum/showthread.php/398-Nth-Such-And-Such-Day-Of-The-Month


So if the year is specified in cell b1, then

date(b1,1,1+7*1)-weekday(date(b1,1,8-5))

Tested and appears to give the right results here:

Year 2024

Month 1 4th Jan 2024 Thursday
2 1st Feb 2024 Thursday
3 7th Mar 2024 Thursday
4 4th Apr 2024 Thursday
5 2nd May 2024 Thursday
6 6th Jun 2024 Thursday
7 4th Jul 2024 Thursday
8 1st Aug 2024 Thursday
9 5th Sep 2024 Thursday
10 3rd Oct 2024 Thursday
11 7th Nov 2024 Thursday
12 5th Dec 2024 Thursday


--
Harriet Bazley == Loyaulte me lie ==

Anything can happen in the next half-hour....

[John Harrison]

In article <2c10517c5...@bazleyfamily.co.uk>,

Harriet Bazley <li...@bazleyfamily.co.uk> wrote:
> Cribbed from an Excel forum:
> date(YYY,MMM,1+7*NNN)-weekday(date(YYY,MMM,8-5)) will give the
> Nth Thursday in the month MMM of the year YYY.

> So if the year is specified in cell b1, then
> date(b1,1,1+7*1)-weekday(date(b1,1,8-5))

Many thanks. I actually want week zero but that works as well

[Stuart Swales]

On 05/07/2024 10:22, John Harrison wrote:
> I want to do something that I feel ought to be easy but I can't work out a
> way to combine Firewokz' functions to achieve it.
>
> I have a spreadsheet in which I enter values every Thursday. The rows are
> numbered 1 to 52 (or 53) but I would like a column to show the actual
> date, which would make it easier when referring back.
>
> The obvious way would be to enter the date in week zero for the relevant
> year (eg 28 December 2023 for this year) and have a formula in each row
> that 'adds a week' to the one above.
>
> I've looked at the time/date functions but so far failed to work out how
> to combine them to get the desired result. Any suggestions?

With the initial date in the top row, just add 7 each time. If there's
no time component, adding a number is adding days.

You could also use (7*(row_number-1) + 4) as the day parameter to

DATE( year:Number, month:Number, day:Number )

with current YEAR(NOW()), month 1, noting that DATE copes with the day
field overflow sensibly; 4 being Thursday.

Stuart
--
Stuart Swales


--
This email has been checked for viruses by Avast antivirus software.
www.avast.com

[John Harrison]

In article <3a32a2e1-b908-4b10...@gmail.com>,

Stuart Swales <stuart.swale...@gmail.com> wrote:
> If there's no time component, adding a number is adding days.

That's interesting - having the addition work on the least significant
time unit used.

My initial attempts at 'adding one unit' failed because I assumed it would
work like other spreadsheets, notably Eureka (which is what this
spreadsheet ran on until it exceeded the column limit) where date/time is
held as a large number of seconds from a past base date. I obtained the
value for one week by subtracting two dates a week apart and then used
that multiplied by the week number.

Obviously (with hindsight) that didn't work with FireWorkz, which is why I
gave up on simple incrementing.

Thanks to all who offered help.