CODEWITHSUNDEEP
dategoogle-sheets
FlorianT.
FlorianT.

Reputation: 873

Display a date in Google Sheets that increments by two weeks every other week

I would like a spreadsheet row to contain the date of today, but only on every other Thursday, changing at 9:30 am. To give you an example:

Next thursday the 21.07.16 it shell contain "21.07.16". Until in exactly 14 days on thursday the 4.08.16 it shell contain this date and than change to 4.08.16.

Also I would like this change to happen at 9:30 am.

I can not think of a way how to do this. Can you point me into a direction?

Upvotes: 1

Views: 5867

Answers (1)

user3717023
user3717023

Reputation:

One has to set a starting datetime somewhere in the past, such as July 7, 2016, at 9:30am.

Then find the difference between the current and the starting datetime. Truncate this difference down to a multiple of 14, and add this value to the starting datetime.

The datetimes are represented in Sheets numerically as the number of days since December 30, 1899. In this system, 2016-07-07 9:30 is about 42558.4 So the formula would be 

=42558.4 + 14*floor((now()-42558.4)/14)

The output should be formatted as a date.

A less cryptic version is

=value("2016-07-07 09:30") + 14*floor((now() - value("2016-07-07 09:30"))/14)

(value follows the local convention for parsing dates, but I hope the format I used will be understood universally.)

Upvotes: 4

Related Questions