A quick method of determining the number of days in a month in WebIntelligence is through the use of the **LastDayOfMonth** function.

For a specified date, the **LastDayOfMonth** will give you the last day in that month.

**=LastDayOfMonth(CurrentDate())**

To extract the number of days, we want to use the first two digits of this result. The following formula will acheive that:

**=Left(FormatDate(LastDayOfMonth(CurrentDate());”dd/MM/yyyy”);2)**

We’ve added two functions here. The first is **FormatDate**.** LastDayOfMonth** returns a value held as a date field. We ultimately want the first two digits of this field. However, using the **Left()** function means we have to have a string input, not a date input. We have to convert the input into a string format first.

This requires two steps.

The first is to convert the **LastDayOfMonth** value into a string. We do this using the **FormatDate **function:

**=FormatDate(LastDayOfMonth(CurrentDate());”dd/MM/yyyy”)**

We now have a string.

We then extract the first two digits to give us the number of days in a month:

**=Left(FormatDate(LastDayOfMonth(CurrentDate());”dd/MM/yyyy”);2)**

If we want to subsequently use this in a calculation, we need to further manipulate this value and convert it to a number. This is achieved using the **ToNumber** function:

**number ToNumber(string number_string)**

This takes a string value and converts it to a number.

In our case, we create the following formula:

**=ToNumber(Left(FormatDate(LastDayOfMonth(CurrentDate());”dd/MM/yyyy”);2))**

We now have the number of days for a particular month that we can use in our calculations.

**Missing FirstDayOfMonth() function…**

Sadly, there isn’t a function to determine the first day of the month. However, we have a method of extracting the last day of the month using **LastDayOfMonth()** and using the formulas described above, we can work out the first day:

=**RelativeDate(LastDayOfMonth(CurrentDate());-ToNumber(Left(FormatDate(LastDayOfMonth(CurrentDate()) ****;”dd/MM/yyyy”);2))+1)**

Despite looking complex, we are subtracting the number of days in the month (plus one) from the last day of the month. If we subtracted the total number of days in the month, we would end up with the last day of the previous month! We don’t want this. So we add a 1 to give us the first day.

Also note that in order to subtract days, we have *prefixed* the **ToNumber** function with a minus symbol.

**Update:**

Hat tip to **JB** for pointing out that we can avoid the string conversion by using the **DayNumberOfMonth** function.

Hence, to calculate the last day of the month:

**=DayNumberOfMonth(LastDayOfMonth(CurrentDate()))**

And the first day of the month:

**=DayNumberOfMonth(RelativeDate(CurrentDate(); 1-DayNumberOfMonth(CurrentDate())))**

March 21, 2012 at 4:01 pm

Couldn’t you also use the following to avoid converting to a string and parsing:

NumberOfDaysInMonthOfObject

=DayNumberOfMonth(LastDayOfMonth([Action Time]))

or

NumberOfDaysInMonthOfCurrentDate

=DayNumberOfMonth(LastDayOfMonth(CurrentDate()))

Similarly, FirstDayOfMonth

=RelativeDate([Action Time]; 1-DayNumberOfMonth([Action Time]))

or

=RelativeDate([Action Time]; 1-DayNumberOfMonth(CurrentDate()))

March 26, 2012 at 1:01 pm

Jermimiah

Thanks for your comment, and you are right! I completely overlooked the DayNumberOfMonth function.

This will make things a bit simpler for our readers.

I will update the post.

March 26, 2012 at 2:23 pm

This is a terrific blog!

March 27, 2012 at 8:27 am

Thank you for your kind words.

April 23, 2012 at 9:33 am

nice posting. i have a question. how i can get LastDayOfMonth from period example: MAR 2012

April 23, 2012 at 10:36 am

Hamidy

You can use:

=LastDayOfMonth(ToDate(“MAR 2012″;”MMM yyyy”))

The last part tells Web Intelligence the date format of the string you are passing in.

September 23, 2013 at 5:57 pm

hi how can we get firstday of month given ”MMM yyyy” format i.e MAR 2013 similar to LastDayof Month shown in posts

October 1, 2013 at 11:55 am

There isn’t a FirstDayOfMonth function that I am aware of.

However, I did write an article which described a workaround:

July 19, 2012 at 3:54 pm

This is great, i’ve also been hunting around for a calculation of ‘work days’ in a month? Also, is there a way to calculate the number of workdays between any two dates?

July 20, 2012 at 11:59 am

Hep

Thanks for your feedback.

Working days in a month is slightly more tricky as you may need to take into account local holidays, etc. This varies from country to country.

They may be a way around this by using the MOD function. I will try to work an example and include here.

July 20, 2012 at 3:44 pm

That’s a good point. I think “working days, not accounting for holidays” is probably all that could be calculated without using calendars. Not sure how to hook in custom calendars to the calculation anyway.

August 2, 2012 at 5:11 am

Hello,

Really a very nice post

I have requirement, where in need to calculated turn time between 2 dates i.e. Start Date – End Date

The catch is if End Date is Sunday than the Time calcuation should be till Saturday.

Eg.

Start Date 12-JUL-2012 00:09:25

End Date 15-JUL-2012 22:08:35

As 15-JUL-2012 is Sunday, while calculation I should take till 14-JUL-2012 24:00

I have used below formula to minus one day if the End Date is Sunday

=If(DayNumberOfWeek([End Date])=7) Then RelativeDate([End Date];-1)

However the date/time which is get is 14-JUL-2012 22:08:35

How can make this to 14-JUL-2012 24:00. I want it in date format to use it in calculation

December 13, 2013 at 4:41 pm

Great Post!!! Helped me a lot. Thanks,