Road map to the xlf-ymd-selector file
1. YMD selector
The xlf-ymd-selector file described in this module contains both Data Validation and Form Control versions of the Year-Month-Day worksheet selector interface. The available selector dates, described as the CurrentTDlist, are geared to trading days on the Australian Stock Exchange (ASX). Assuming a 20 minute lag in available data, the list updates at 10:30 am each trading day. Features of the file are summarised in the following sections. A copy of the file is available for download.
1.1 Data Validation (DV) version
Data validation settings:
- Year - E4: List > Source:
=YearList
- Month - E5: List > Source:
=MonthList
- Day - E6: List > Source:
=DayList
1.2 Form Control (FC) version
Form Control settings:
- Year - Input range:
Year.List
, Cell link:Y
- Month - Input range::
Month.List
, Cell link:M
- Day - Input range:
Day.List
, Cell link:D
2. YMD dates
Year-Month-Day values are derived from the CurrentTDlist (section 2.2).
2.1 YMD list constructor
Each list in figure 3 is returned by spilled formula in row 3.
Figure 3 WS formulas (row 3)
NAME CELL YearList :: M3: = UNIQUE(YEAR(CurrentTDlist)) MonthList :: N3: = UNIQUE(MONTH(FILTER(CurrentTDlist,YEAR(CurrentTDlist) = Year))) DayList :: O3: = DAY((FILTER(CurrentTDlist,(YEAR(CurrentTDlist) = Year) * (MONTH(CurrentTDlist) = Month))))
Conditional formatting showing current selector settings:
- YearList: Formula
=M3=Year
- MonthList: Formula
=N3=Month
2.2 Trading day lists
TDAnchor is the first trading day - cell J3.
Figure 4 WS formulas
NAME CELL TDAnchor J3: = LastBusDay J4: = IFERROR(IF(WORKDAY(J3,-1,Holidays) >= FirstBusDay,WORKDAY(J3,-1,Holidays)),".") K3: = OFFSET(TDAnchor,TDposn-1,0,TDminPosn-TDposn+1)
2.3 Trading day WorkArea
The first value in the CurrentTDlist is conditional on the computer time and the most recent holiday data (LastBusDay - section 2.3)
Figure 5 WS formulas
Now :: N28: = MIN(TDAnchor,NOW()) Ten.30 :: N29: = IF(Now - INT(Now) >= TIME(10,30,0),Now,Now - 1) TradDay :: N30: = WORKDAY(WORKDAY(Ten.30,1,Holidays),-1,Holidays) TDposn :: N31: = MATCH(TradDay,TradingDays,0) TDminPosn :: N32: = MATCH(MIN(TradingDays),TradingDays,0) TDminVal :: N33: = OFFSET(TDAnchor,COUNT(INDIRECT("C"&COLUMN(TDAnchor)&":"&"C"&COLUMN(TDAnchor),FALSE)) - 1,0,1,1) TDlastFPos :: N34: = ROW(OFFSET(TDAnchor,COUNTA(INDIRECT("C"&COLUMN(TDAnchor)&":"&"C"&COLUMN(TDAnchor),FALSE))-1,0,1,1))-ROW(TDAnchor) - 1 TDavailable :: N35: = TDlastFPos - TDminPosn
TradingDays range defined name
- TradingDays: Refers to:
=OFFSET(TDAnchor,0,0,COUNT(Sheet1!$J:$J))
2.3 Holidays WorkArea
Figure 6 WS formulas
NAME CELL FirstYrHols N38: =YEAR(INDEX(Holidays,1,1)) FirstBusDay N39: =WORKDAY(WORKDAY(DATE(FirstYrHols,1,2),-1,Holidays),1,Holidays) LastYrHols N41: =YEAR(@INDEX(Holidays,ROWS(Holidays),8)) LastBusDay N42: =WORKDAY(WORKDAY(DATE(LastYrHols,12,31),1,Holidays),-1,Holidays)
Holidays range defined name
- Holidays: Refers to:
=OFFSET(Sheet1!$R$3,0,0,COUNT(Sheet1!$U:$U),8)
- Download the file: xlf-ymd-selector.xlsx [93 KB]
- This example was developed in Microsoft Excel for Microsoft 365 MSO
- Published: 1 June 2021
- Revised: Saturday 25th of February 2023 - 10:13 AM, [Australian Eastern Standard Time (EST)]