Clear all

[Solved] How to map Year and Day_of_year to actual date

Posts: 2
Topic starter
Active Member
Joined: 3 months ago

I have a time index consisting of actual dates:

Time := Sequence( start_date, end_date )

The start_date and end_date aren't on even year or even month boundaries.

I also have a 2-D representation of data across time that uses these two indexes:

Index Year := 2019 .. 2021

Index Date_of_year := ['1-Jan', '2-Jan', ..., '31-Dec' ]

BTW, my actual definitions for these are:

Index Year := Unique(DatePart(Time,'Y'))

Index Date_of_year := Local d := Sequence(1-Jan-2020,31-Dec-2020) Do DatePart(d,'D') & '-' & DatePart(d,'EEE')


Note that Date_of_year includes '29-Feb', even though non-leap years don't have data for that date (my arrays have Null in that case). 

I want to compute a map from the 2-D Year x Date_of_year to the 1-D Time. In other words, the map would be a 2-D array, indexed by Year and Date_of_year, and each cell of that array would be the corresponding full date as it appears in the Time index. Some cells won't be valid dates (like 29-Feb-2021) and some will be outside by Time range since my Time index doesn't start on even year boundaries. Ideally those cells would be Null. 

So, for example, the cell at [ Year = 2021, Date_of_year = '23-Jul' ] would be the date 23-July-2021.

The attached file sets up the problem -- I need the definition for Y_and_D_to_Time.

2 Replies
Posts: 9
Joined: 11 years ago
Local d := ParseDate(Day_of_year&"-"&Year);
d := if Day_of_year='29-Feb' and DatePart(d,'D')<>29 then null else d;

Second line sets 29-Feb to Null for the non-leap years.  The last line sets all cells in the map to Null if they don't exist in your Time index. In terms of using this as a mapping, you wouldn't need to do that last step (although it doesn't hurt), so this would be sufficient:

Local d := ParseDate(Day_of_year&"-"&Year);
if Day_of_year='29-Feb' and DatePart(d,'D')<>29 then null else d

Either case should work fine to expand a 1-D array, x, that is indexed by Time to a 2-D array using

x[ Time = Y_and_D_to_Time ]


Posts: 8
Eminent Member
Joined: 4 months ago
Local isLeap := Mod(Year,4)=0 and Mod(Year,100)<>0 or Mod(Year,400)=1;
Local offset := 
	if Day_of_year='29-Feb' and not isLeap then null 
	else if @Day_of_year > @[Day_of_year='29-Feb'] and not isLeap then @Day_of_year-2 
	else @Day_of_year-1;

MakeDate(Year,1,1) + offset
Scroll to Top