Share via

How to get day numbers from day names?

Anonymous
2025-06-20T23:58:49+00:00

Hi,

I'm using Excel 2024. I'm working with a table that has a column of day names (Mon, Tue, Weds, etc), but no column of dates. I know how to use Power Query to pull the day numbers from a Date column, but since I have no Date column, I'm stuck. Is there a way to get day numbers from day names in Excel, Power Query, or Data Model?

What I'm trying to do is use a Day slicer on my table, but the slicer is in alphabetical order. I'd like to put the slicer in chronological order. From what I've read, I'd need day numbers to do that. Unless you know another way to accomplish that.

Here's my day name column in Power Query:

Thank you.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2025-06-21T01:19:05+00:00

    Hello mariner blue,

    Thank you for visiting the Microsoft Community.

    To extract numerical values corresponding to weekday names, you may employ the following formula (assuming Monday as the first day; should you prefer Sunday as the starting day, simply adjust the numerical assignments accordingly):

    =@SWITCH([@[Flight Day]], "Mon", 1, "Tue", 2, "Wed", 3, "Thu", 4, "Fri", 5, "Sat", 6, "Sun", 7)

    This is a spill array formula, therefore you need only input it once in the row containing the first weekday name. The implementation produces results as illustrated below:

    Does this adequately address your inquiry? I trust this proves beneficial.

    Best regards

    Huy-K | Microsoft Community Support Specialist

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-06-24T12:45:04+00:00

    Hello, mariner

    I am delighted that the provided steps proved effective. Should you encounter any further inquiries in the future, please feel free to post your questions in our community forum at your convenience.

    Wishing you a most pleasant day ahead.

    Best regards

    Tamara-Hu | Microsoft Community Support Specialist

    Was this answer helpful?

    0 comments
  2. Anonymous
    2025-06-21T03:09:04+00:00

    Hi,

    Yes, thank you very much, that works. Great formula! I appreciate your reply. Oh, and thank you for explaining how to adjust it for Sunday as the first day, which is what I wanted.

    Was this answer helpful?

    0 comments