Use Date and Time Functions in Report Studio

ClickTime’s Report Studio offers a wide variety of date and time functions to help you calculate durations, extract time components, and manipulate or format date values. These functions can be used in custom attributes or calculated metrics to group, filter, and analyze time-based data.

Quick Reference: Common Date & Time Functions

Date Calculations

AddDays(Date, Number)

Adds the specified number of days to a given date.

Example:
AddDays([Submitted Date], 7)
Returns: 2025-04-17 if the submitted date is 2025-04-10

AddMonths(Date, Number)

Adds the specified number of months to a date.

Example:
AddMonths([Start Date], 2)
Returns: 2025-07-01 if start date is 2025-05-01

DateDiff(Start, End)

Returns the number of days between two dates.

Example:
DateDiff([Project Start Date], [Project End Date])
Returns: 9 if the project ran from 2024-04-01 to 2024-04-10

DaysBetween(Date1, Date2)

Similar to DateDiff, returns the number of days between two dates.

Example:
DaysBetween([Submitted Date], CurrentDate())

MonthsBetween(Date1, Date2)

Returns the number of full months between two dates.

Example:
MonthsBetween([Hire Date], CurrentDate())
Returns: 6 if 6 full months have passed

Time Extraction

Hour(DateTime)

Extracts the hour (0–23) from a date-time value.

Example:
Hour([Created DateTime])
Returns: 14 from 2025-06-18 14:45:00

Minute(DateTime)

Extracts the minute portion.

Example:
Minute([Start Time])
Returns: 45 from 09:45:00

Second(DateTime)

Extracts the seconds.

Example:
Second([Start Time])
Returns: 30 from 10:15:30

MilliSecond(DateTime)

Extracts the milliseconds portion.

Date Extraction

DayOfMonth(Date)

Returns the day of the month (1–31).

Example:
DayOfMonth([Worked Date])
Returns: 13 from 2025-05-13

DayOfWeek(Date)

Returns the day of the week (1 = Sunday, 7 = Saturday).

Example:
DayOfWeek([Worked Date])
Returns: 3 from a Tuesday

DayOfYear(Date)

Returns the day number of the year (1–366).

Example:
DayOfYear("2025-01-15")
Returns: 15

Month(Date)

Extracts the month number (1–12).

Example:
Month([Worked Date])
Returns: 5 for May

Quarter(Date)

Returns the calendar quarter number (1–4).

Example:
Quarter([Worked Date])
Returns: 2 for May

Year(Date)

Extracts the year from a date.

Example:
Year([Worked Date])
Returns: 2025

Week(Date)

Returns the week number of the year.

Example:
Week("2025-05-13")
Returns: 20

Fiscal Periods

FiscalMonth(Date)

Returns the fiscal month based on your organization’s fiscal calendar.

FiscalQuarter(Date)

Returns the fiscal quarter.

FiscalWeek(Date)

Returns the fiscal week number.

FiscalYear(Date)

Returns the fiscal year.

Note: Fiscal periods reflect your company’s configured fiscal settings in ClickTime.

Start and End of Periods

MonthStartDate(Date)

Returns the first day of the month.

Example:
MonthStartDate("2025-05-13")
Returns: "2025-05-01"

MonthEndDate(Date)

Returns the last day of the month.

Example:
MonthEndDate("2025-05-13")
Returns: "2025-05-31"

QuarterStartDate(Date)

Returns the first day of the calendar quarter.

Example:
QuarterStartDate("2025-05-13")
Returns: "2025-04-01"

WeekStartDate(Date)

Returns the first day of the week (Sunday or Monday depending on your settings).

Example:
WeekStartDate("2025-05-13")
Returns: "2025-05-12" if week starts on Monday

YearStartDate(Date)

Returns the first day of the year.

Example:
YearStartDate("2025-05-13")
Returns: "2025-01-01"

YearEndDate(Date)

Returns the last day of the year.

Example:
YearEndDate("2025-05-13")
Returns: "2025-12-31"

Conversions & Current Time

CurrentDate()

Returns today’s date.

CurrentTime()

Returns the current time (hour:minute:second).

CurrentDateTime()

Returns the full current date and time.

Date(Text)

Converts a string into a date value.

Example:
Date("2025-05-13")

ToDateTime(Text)

Converts a text string to a date-time value.

Example:
ToDateTime("2025-03-15 09:00:00")
Returns a valid date-time object

Tips for Using Date Functions

• You can combine multiple date functions in a formula
  Example: MonthStartDate(CurrentDate())
• Always use square brackets [] around attribute names
  Example: [Worked Date]
• Text values like date strings must be in quotes " "
  Example: "2025-05-01"

Exploring Further

ClickTime's Report Studio may offer other helpful date and time functions beyond those listed here. Explore the Function List in the Formula Editor to see the full range of capabilities available to you. Be sure to read the function descriptions for syntax guidance and supported input types.

These functions are especially powerful when building reports for time tracking, pay period analysis, budgeting by fiscal quarter, and forecasting hours worked.