# Blog

# Dates - How to get a numbered list of days or months, starting from a specified day or month

For date or scheduling diary type apps, it can be very useful to retrieve a list of days or months that begin on specific day or month. This post describes some formulas that we can use.

## 1. Get a list of months and month numbers

To return a list of month and month numbers, we can use the following formula:

With({data: RenameColumns(Calendar.MonthsLong(),"Value", "MonthName")},

ForAll(Sequence(12),

Patch(Last(FirstN(data,Value)),

{MonthNumber:Value}

)

)

)

## 2. Get a list of days and day numbers

We can adapt the above formula to return day and they numbers using the following formula:

With({data: RenameColumns(Calendar.WeekdaysLong(),"Value", "DayName")},

ForAll(Sequence(7),

Patch(Last(FirstN(data,Value)),

{DayNumber:Value}

)

)

)

## 3.Get week days, starting from a specified day

By default, the calendar function returns Sunday as day 1. If we want to specify a different day that corresponds to day 1, we can use the formula beneath. Here, startDay defines day one. In this example, we set this to 3 in order to specify that Wednesday should be day one.

With({data: RenameColumns(Calendar.WeekdaysLong(),"Value", "DayName"),

startDay:3},

ForAll(Sequence(7),

Patch(Last(FirstN(data,Value)),

{DayNumber:If(Value<=startDay, 7 +(Value-startDay), Value-startDay) }

)

)

)

SortByColumns(

With({data: RenameColumns(Calendar.WeekdaysLong(),"Value", "DayName"),

startDay:3},

ForAll(Sequence(7),

Patch(Last(FirstN(data,Value)),

{DayNumber:If(Value<=startDay, 7 +(Value-startDay), Value-startDay) }

)

)

),

"DayNumber"

)

## 4. Get 12 months, starting from a specified month

Let's suppose that the financial year for a company starts in April. We can use the following formula to number months, with month one starting in April.

SortByColumns(

With({data: RenameColumns(Calendar.MonthsLong(),"Value", "MonthName"),

startMonth:4},

ForAll(Sequence(12),

Patch(Last(FirstN(data,Value)),

{MonthNumber:If(Value<startMonth,

13 +(Value-startMonth),

Value-(startMonth-1)

)

}

)

)

),

"MonthNumber"

)

## Conclusion

- Categories:
- dates

__Related posts__

- Dates - How to convert a month name (eg January, Feburary, March) to month number (eg, 1, 2, 3)
- Dates - How to format date day numbers with suffix (st nd rd th)
- Formula - How to get the dates for the current week
- Dates - How to calculate UK tax weeks
- Dates - How to convert dates from MM DD YYYY to DD MM YYYY and vice versa
- Dates - How to detect overlapping times / scheduling conflicts
- Dates - How to display or convert Excel date serial numbers
- Dates - How to get the start date from week number
- Dates - How to check if a year is a leap year
- Dates - How to get the week number for a given date
- Dates - How to get the nth weekday for a month
- Dates - How to get the last weekday for a month
- Controls - How to display dates in a combo box
- Search - How to filter records by month and year using a delegable expression