Create Ranges of Dates and Return a Value in VBA Code

[Denny57]

I am looking to apply a value to a date which falls in a range of dates without creating a lookup table. Knowing the issues of using dates in VBA.
I would be interested in guidance of the best approach as there can be an initial 24 date ranges to be used so the lookup table would require 730 rows of information

Example
If "Date" is in the range 01/01/2025 to 28/01/2025 return a value of "Pay 1 2025"
If "Date" is in the range 29/01/2025 to 25/02/2025 return a value of "Pay 2 2025"

I have considered using IF, ELSEIF, THEN but am not sure if the date values will work.

For information.
The value of "Date" will be in a TextBox (txtDate) and formated as dd/mm/yyyy
The return value will be returned to another TextBox (txtPayPeriod)

Best process guidance would be very much appreciated

 

[iggydarsa]

Not sure about the textboxes but I hope this can give you an idea.
I see in your example your pay period is every 28 days, if that's the case, this formula will tell you the Pay Period like in your example by looking at the date in cell A1

="Pay " & INT((A1-DATE(YEAR(A1), 1, 1))/28)+1 & " " & YEAR(A1)