This requires Builder privileges for utilization.
| Function Returns | Syntax | Data Type | Results example |
| Calendar Year | =YEAR(datetime_field) | INT | 2015 |
| Calendar Quarter | =ROUNDUP(MONTH(datetime_field))/3,0) | INT | 2 |
| Day of Year | =datetime_field-DATE(YEAR(datetime_field),1,1)+1 | INT | 136 |
| Month as Number | =MONTH(datetime_field) | INT |
5
|
| Month Name | =TEXT(datetime_field,"mmmm") | STRING | February |
| Short Month Name | =TEXT(datetime_field,"mmm") | STRING | Feb |
| First Day of Month | =DATE(YEAR(datetime_field),MONTH(datetime_field),1) | DateTime | 1/15/2015 |
| ISO Week Number | =ISOWEEKNUM(datetime_field) | INT | 42 |
| Weekday Name | =TEXT(datetime_field,"dddd") | STRING | Friday |
| Short Weekday Name | =TEXT(datetime_field,"ddd") | STRING | Fri |
| Calendar Weekday as Number | =WEEKDAY(datetime_field) | INT | 5 |
| Calendar Day in Month | =DAY(datetime_field) | INT | 23 |
| Boolean Weekend | =IF(OR(WEEKDAY(datetime_field)=1,WEEKDAY(datetime_field)=7),1,0) | INT |
1 = True 0 = False |
| Hour in Day | =HOUR(datetime_field) | INT | 13 |
| Minute(s) in Hour | =MINUTE(datetime_field) | INT | 56 |
| Second(s) in Minute | =SECOND(datetime_field) | INT | 42 |
| Fiscal Quarter (July - June) |
="FY"&(IF(MONTH(datetime_field)>6,RIGHT(YEAR(datetime_field),2)+1, RIGHT(YEAR(datetime_field),2))&"-"&CHOOSE(MONTH(datetime_field), "Q3","Q3","Q3","Q4","Q4","Q4","Q1","Q1","Q1","Q2","Q2","Q2")) | STRING | FY15-Q4 |
| Fiscal Quarter (October - September) |
="FY"&(IF(MONTH(datetime_field)>9,RIGHT(YEAR(datetime_field),2) +1,RIGHT(YEAR(datetime_field), 2))&"-"&CHOOSE(MONTH(datetime_field), "Q2","Q2","Q2","Q3","Q3","Q3","Q4","Q4","Q4","Q1","Q1", "Q1")) | STRING | FY15-Q4 |
| Date Difference | =datetime_field_2 - datetime_field_1 | INT | 41 |
All formulas in this guide are provided as-is for reference purposes. Our Technical Support Team will not assist with authoring new formulas. Please check out Microsoft's Date and time functions (reference) documentation for more information on anything concerning Datetime.
Comments
0 comments
Please sign in to leave a comment.