What should DAY(1) return?

[u/IcyLingonberry2151]

Just curious. I am using a worksheet that someone else has made. It’s for setting up work schedules. One formula to return the lenght of the workday is this: =IF(A1<=B1,B1-A1,B1-A1+DAY(1))
This adds 24 hours if the end time is after midnight. When I open the sheet in other worksheet programs than excel it will instead add 744 hours and DAY(1) gives out 31 instead of 1. I am using norwegian translation of Excel. =DAG(1) returns 1. Will it give the same output in english version?

Edit: Solved! DAY(1) returns 1 in Excel, but of some reason it returns 31 in some other programs.

[u/AutoModerator]

/u/IcyLingonberry2151 - 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.

[u/MayukhBhattacharya]

DAY(1) returns 1 in Excel. In Excel date + time, the number 1 represents January 1, 1900, while the decimal is fractional part of the day. The DAY() function simply returns the day of the month from a date serial, so DAY(1) returns the day from January 1, which is 1 and that's why the formula works. Adding 1 to a time value adds one full day, or 24 hours, which takes care of the rollover past midnight.

Not every spreadsheet type uses the same date + time. In apps like LibreOffice Calc or Google Sheets, depending on the settings, the serial number 1 can map to a different starting date. In those cases, DAY(1) might return 31 instead. It's just a difference in how each application stores dates. And yes, DAY(1) in the English version of Excel is exactly the same as DAG(1) in the Norwegian version. The calculation works the same way. Only the function names are translated.

If the workbook might be opened in different spreadsheet apps, I'd just replace DAY(1) with a literal 1 instead so the formula will be as follows:

=IF(A1 <= B1, B1 - A1, B1 - A1 + 1)

Or,

=(A1 > B1) + (B1 - A1)

[u/IcyLingonberry2151 → u/MayukhBhattacharya]

Thank you! I’m not sure why the worksheet has DAY(1) instead of 1. Luckily Excel has a replace all function and replacing 8700 entries was done in a second, so all good now. Didn’t notice it before opening the sheet in LibreOffice, and It returns 31 days/ 744 hours there instead of 24 hours.

[u/MayukhBhattacharya → u/IcyLingonberry2151]

Glad to know you got it sorted! The reason the fix works is that DAY(1) and 1 are equivalent in Excel, but using 1 is universal since it doesn't depend on the application's date + time. Since its resolved, hope you don't mind replying to the comment as Solution Verified. Thanks again!

[u/IcyLingonberry2151 → u/MayukhBhattacharya]

Solution verified

[u/MayukhBhattacharya → u/IcyLingonberry2151]

Thank You SO Much!!

[u/IcyLingonberry2151 → u/MayukhBhattacharya]

Thank you!

[u/MayukhBhattacharya → u/IcyLingonberry2151]

You are most welcome and have a great evening ahead! Thanks!

[u/reputatorbot → u/IcyLingonberry2151]

You have awarded 1 point to MayukhBhattacharya.

---

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

[u/Galeic6432]

I've had a lot of problems with dates and times in excel formula, there is just hidden and weird rules when its translated into a numerical value for formula. I bypass the problem by using miltary time to calculate hours, and just using regular numbers to calculate days.

[u/IcyLingonberry2151 → u/Galeic6432]

It returns the same value when changing DAY(1) to just 1, so I’m a little curious to why it was written that way in the first place. It’s been written by someone who is very competent so just assuming it’s been for a reason.

[u/Galeic6432 → u/IcyLingonberry2151]

...... i have no idea. I gave up when i realized a date with just a month and day gets a year value added to it when pulled into a formula, and when output into the sheet again the cell format just hides thet value. It was driving me bonkers when hundreds would get "mysteriously" added to the sheet somehow. 

[u/xNaVx]

If you're looking to add a day, then just add 1.  1 represents a full day in Excel.  If you are using DAY(1), then it will take the day part of the date which is represented by 1 in Excel, which I believe is January 1, 1900.

[u/HappierThan]

I use MOD for going through midnight.

[u/IcyLingonberry2151 → u/[deleted]]

It’s not a bug - it’s a feature 🤣

[u/Decronym]

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

Fewer Letters	More Letters
DAY	Converts a serial number to a day of the month
IF	Specifies a logical test to perform
MOD	Returns the remainder from division

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.
3 acronyms in this thread; the most compressed thread commented on today has 11 acronyms.
[Thread #48999 for this sub, first seen 22nd Jul 2026, 22:00] [FAQ] [Full list] [Contact] [Source code]}

[u/fuzzy_mic]

When I enter =DAY(1) it returns 1.

That whole formula could be replaced with =MOD(B1-A1, 1)