by “Day function” will return the day part of a date in a numeric format. For example, in the data 21st of January 2020, it will return 21. You can also provide a serial number representing the elapsed days since a reference date, in this case, December 31, 1899 (serial number 1).
Usage
Here’s how to use it referencing to another column:
=DAY([Birthday])
will return 11 if the date in the Birthday column is the 11th of any month or year.
Or if you have a date in mind:
=DAY("11-Dec-2020")
will return 11
If you have a serial number, here’s how to get the date:
=DAY(44176)
will return 11 because 44176 is 11th of December 2020
I go into a lot of detail regarding serial numbers on the “Now Function” and “Today Function,” as well as an article that explains how to convert this into a date using Power Automate. To make a long story short, the data is stored as a reference or a serial number, so it’s possible to store it and convert it to any timezone or date format without interfering with the storage itself. This is all inherited from Excel’s function storage and parsing, so if you know how it works in Excel, you also know how it works here.
Limitations
If you’re using the serial number, you have the hard limitation of the number 1. If you use a negative number, the function won’t return an error but will display “#Num!” as a result. The same if you try with the reference date. It won’t return an error, but it will display ”#Value!”.
Recommendations:
- There are other functions for the other elements that you can find in my SharePoint’s Function Reference. Don’t try to do the math and use the pre-defined functions for each part of a date. If you can’t find it, please submit it to me on Twitter or by email, and I’ll be happy to add it.
- If you deal with many timezones, always use the serial number to do the conversion and manipulation of dates; otherwise, you’ll get into trouble with timezones. You can also use the proper date functions to add or subtract elements from a date to be sure.
- Always check for error codes because the function itself often won’t return any errors even if the date is incorrect.
- Don’t nest inside another function as a complex formula. Check first if you can get the day in a calculated column and then perform the actions that you need. Otherwise, you’ll have a complex formula and hard to debug in the case of an invalid result.
Sources:
Microsoft’s Day Function Reference
Introduction to SharePoint formulas and functions
Back to SharePoint’s List Function Reference.
Photo by Adam Tinworth on Unsplash
