How to calculate the number of days in a month in WebIntelligence

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())))

About these ads

13 Responses to “How to calculate the number of days in a month in WebIntelligence”

  1. jerimiahbaldwin Says:

    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()))

  2. midyx Says:

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

    • weldblog Says:

      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.

      • pavani Says:

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

      • weldblog Says:

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

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

  3. hep Says:

    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?

    • weldblog Says:

      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.

      • hep Says:

        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.

  4. Shashi Says:

    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

  5. Joel de Souza Says:

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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 113 other followers

%d bloggers like this: