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

xlf-ymd-selector-dv — **Fig 1: YMD data validation version** - cell based drop-down lists

Data validation settings:

Year - E4: List > Source: =YearList
Month - E5: List > Source: =MonthList
Day - E6: List > Source: =DayList

1.2 Form Control (FC) version

xlf-ymd-selector-fc — **Fig 2: YMD form control version** - Combo Box (Form Control) drop-down lists

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.

xlf-ymd-trading-day — **Fig 4:Trading day lists** - all trading days for span of Holiday range (column J), all trading days from current trading day and earlier (column K)

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)

xlf-ymd-tradingday-workarea — **Fig 5: Trading day WorkArea** - list attributes of **TD...** values

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

xlf-holidays-workarea — **Fig 6: Holidays WorkArea** - first and last trading from **Holidays** range

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)]