Jet Reports Homepage |  Community Forum |  Downloads |  Submit A Ticket |  Jet Express Support
Feedback

Dates


Five date related functions in Excel make working with dates much easier. The functions are as follows.

=Date(Year, Month, Day)

=Day(Date)

=Month(Date)

=Year(Date)

=Text(date, format)

To find the first day of the month from a date entered in D4, you could enter the following formula.

=Date(Year(D4), MONTH(D4), 1)

Since dates are stored in Excel as integers, you can find the last day of the month from a date entered in D4 with the following formula.

=Date(Year(D4), MONTH(D4)+1, 1)-1

Since you can disassemble a date easily into the year, the month and the day, it is also easy to calculate the start of the year for the date in D4 with the following formula.

=Date(Year(D4), 1, 1)

If your fiscal year started in July instead of January, the formula is similar. The IF formula in the Year parameter of the following formula determines, in this case, if the start of the fiscal year is the same as the calendar year (i.e. Sept 6, 2003 is in the 2003 Fiscal Year), or if you need to subtract 1 from the calendar year (May 15, 2003 is in the 2002 Fiscal Year).

=Date(If(Month(D4)<7,Year(D4)-1, Year(D4)),7, 1)

To build a date range from two dates, use the NP ("DateFilter") function. If the start of the month was in cell D4 and you wanted to calculate a filter for the whole month, the formula for the date range would be:

=NP("DateFilter",D4, Date(Year(D4), MONTH(D4)+1,1)-1)

The function above looks complicated, but it is just an NP function with the end of month formula previously listed.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments