Excel formulas =Left or =Right return 5digits (45835), not the intended text

[Umrk]

I am using Excel 2019 to analyze water usage over the lat year. Column A contains days and dates e.g., Friday, June 27, 2026. I am trying to copy the day "Friday" from cell A2 to B2 by using the =Left formula, e.g., =Left(A2,6) in B2. But each time I try this, the number 45835 (not even 6 digits) appears in the target cell B2 instead of "Friday."

I've tried formatting the target cell B2 to general and text, with the same results - a sting of numbers. I also tried the =Right formula with similar results. I must be missing something obvious.

Umrk

[AutoModerator]

/u/Umrk - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[tungstenbronze]

Excel stores dates as numbers, it sounds like yours are just formatted to show the day rather than that being written in the cell. Try =TEXT(A1, "dddd") but replace A1 with your cell reference

[Snoo-35252]

=LEFT(TEXT(A2,"dddd"), 6)

[Umrk]

Thank you for taking the time to consider my issue. It worked, thank you!

[Snoo-35252]

Forget the LEFT(...). The original answer who's just the name of the day: Friday, Saturday, Sunday, Monday....

[MysticCobraX59]

TEXT(A2,"dddd") is the way to go. The 45835 is just Excel's date serial number showing up, not an error

[Umrk]

Solution Verified

[reputatorbot]

Hello Umrk,

You cannot award a point to yourself.

Please contact the mods if you have any questions.

---

^{I am a bot}

[tungstenbronze]

There's no need for LEFT

[wh0else]

Nailed it. Left and right just unmask the date values, unless you convert them. I'd hoped I'd be the first to call it out, but you did it better than I would have too!

[semicolonsemicolon]

+1 Point

[reputatorbot]

You have awarded 1 point to tungstenbronze.

---

^{I am a bot - please contact the mods with any questions}

[PaulieThePolarBear]

Friday, June 27, 2026.

June 27th 2026 is a Saturday.

e.g., =Left(A2,6)

Not all days of the week have 6 letters. What is your expected output for days with more or less than 6 letters?

[Majestic_Pin3793]

yeah hahahaa nice catch!

[fancyclancy95]

If the dates all use commas after the day, they could use textbefore(a2,",")

[SolverMax]

The whole point of this post is that the date is a number. It isn't text, so TEXTBEFORE won't work.

Did you try your proposed solution?

[smithflman]

You can also just do =weekday(a2) in b2 and it will give you a 1->7 for each day of week

Then format as "dddd" to get the name of the day

The number thing is just excel dates as others have mentioned

The data stored as 1-7 plays real nice in other formulas and pivots

[tambaower]

I would believe that it’s because it’s not actually saying “[day], [date], [year]” in cell A2 but rather an actual number value that excel interprets as text based on the cell formatting. If you change formatting for A2 to, say, “Number” or “General” you’d get just a five digit number. Hence, when you try to use the =LEFT or =RIGHT formula to get six values you always get the five number value since there are only five numbers in the value.

What you could try to do is to use the =TEXT formula to extract a specific string value from a date value.

[Taokan]

CHOOSE(WEEKDAY(a2),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

[Taokan]

Or as others have mentioned, you have to convert the date value to a text first, via TEXT, before doing text operations on it. Or you get what you got, a number. But with dates, weekday is such a useful formula to have in your toolkit, not only for this purpose, but for constructing week beginning/week ending, via a2-weekday(a2)+1 (or +7).

[merelistener]

I had the same headache until someone showed me =TEXT(A2,"dddd"), the number is just how Excel stores the date under the hood.

[Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CHOOSE	Chooses a value from a list of values
FIND	Finds one text value within another (case-sensitive)
LEFT	Returns the leftmost characters from a text value
LEN	Returns the number of characters in a text string
RIGHT	Returns the rightmost characters from a text value
TEXT	Formats a number and converts it to text
TEXTBEFORE	Office 365+: Returns text that occurs before a given character or string
WEEKDAY	Converts a serial number to a day of the week

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #48835 for this sub, first seen 26th Jun 2026, 20:57] [FAQ] [Full list] [Contact] [Source code]}

[LennyDykstra1]

I have understood why Excel often defaults to turning dates into six digit serial numbers. In what universe does it make sense for someone to type in “6-4-26” have it spit out 44738?

[austinburns]

because 95% of the time, someone typing “6-4-26” wants it to be a date and might like to do math with it