Last Date of the Month

jk0607

Member
Hi,

How to get the last date of a month with out using any code stage?
Any ideas and suggestions appreciated.



Thanks,
 

VJR

Well-Known Member
Hi,

How to get the last date of a month with out using any code stage?
Any ideas and suggestions appreciated.



Thanks,
Hi jk0607,

If you would like to get the last date of the month without using code stage then you could make use of Excel's EOMONTH (End of Month) function.

- In the Blue Prism process either create & open a new Excel each time and then close it after the date is calculated without saving the file.
OR place an Excel workbook at some location which will be used specifically for this purpose. This will need to be placed on all machines if the process runs on multiple machines so the prior option is better.

- Then use the Excel VBO's 'Set Cell Value' action to insert the current date (or any date) whose last date needs to be calculated. Lets say you wrote this on cell A1.

- Then in any of the neighboring cell, lets say B1, use the same action to set the end of month formula as "=EOMONTH(A1,0)"

- This will give the last date of month in cell B1 which can then be read from the process via 'Get Cell Value'

- The file can then be closed without saving.

Refer the below link for the Excel function.
https://www.excel-easy.com/examples/last-day-of-the-month.html
 

VJR

Well-Known Member
Awesome!
but I tried something and it worked with the date functions.
Thanks much appreciated. VJR!
That's even great. Can you post your findings using the Date functions for it to be useful to anyone looking for the same.
 

pjain

New Member
I think this expression will work

DateAdd(5, [Month]-1, MakeDate(31,1, 2018))

We are forming a valid end date for Januray and adding no of [Month] as an input parameter for the month whose last date is required.

This will always return a valid last date of a month
 

VJR

Well-Known Member
Hi pjain,

That expression is hard coded to the year 2018 although you can use it if it serves the purpose of what you are working on.

I just took a look at some vbscripts and found that the Blue Prism functions were giving the equivalent support in providing the desired results.

Below expression supports leap year or any other dates.
The input should be passed as a Date but will need to be customised if one needs to pass only the month and the year because at least both of these are required to consider leap year and month scenarios.


Calc stage with expression:

View attachment 1535171125093.png

Expression (in text format):
AddDays(MakeDate(1, ToNumber(FormatDate(AddMonths([Inputdate], 1), "MM")), ToNumber(FormatDate(AddMonths([Inputdate], 1), "yyyy"))), -1)


The basic logic is as follows:
- AddMonths: Go to the next month of the input date
- FormatDate: To get only the month and the year portion which are required for the MakeDate function
- MakeDate: Format the month and year of that date as the 1st day of month
- AddDays: Subtract 1 day from the 1st day of next month


Leap month and year:

View attachment 1535171267565.png


First month of a year:

View attachment 1535171402379.png


Last month of a year:

View attachment 1535171456389.png


Monthend with 30 days:

View attachment 1535171515591.png
 
Last edited:

bvan

New Member
I think this could help.


AddDays(AddMonths(MakeDate(1, FormatDate(Today(), "MM"), FormatDate(Today(), "yyyy")), 1), -1)

Exactly what I was looking for, except to get last date from prior month I changed the 1 to 0

AddDays(AddMonths(MakeDate(1, FormatDate(Today(), "MM"), FormatDate(Today(), "yyyy")), 0), -1)
 

RenjuNikhil

New Member
Check FormatDate(AddDays(Today(), 1), "dd")="01" expression in decision If yes then we can confirm It is the start of the month
 

Prabhakar Yadav

New Member
to calculate next month first day use the following or many more calcution we can done by using this ,like by changeing the values like of days and months
AddDays(AddMonths("1-"&FormatDate(Today(), "MM yyyy"), 1), -1)
 

Attachments

  • Untitled.png
    Untitled.png
    171.3 KB · Views: 26
Top