Re: Set Month End Date
Hi, Please don't use char-fields like this. They are dependent on the language settings, and will be unpredictable especially on the client side via Active Links. Best Regards - Misi, RRR AB, http://rrr.se The DateNum function is your friend here, try this Have a temp character field and set it to be: Temp Character = (DATENUM(mm, $DATE$) +1) + /1/) + YEAR($TIMESTAMP$) (You may have to reformat this a bit depending on Locale) Then have another set fields into a temp date field that does Temp Date = $Temp Character$ Then one more that does DueDate = $Temp Date$ - 1 I haven't tested this but it should work From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Karthick S Sent: Wednesday, May 07, 2014 3:50 AM To: arslist@ARSLIST.ORG Subject: Set Month End Date ** I need to Set a Month End Date in a field Called Due Date. Please find the below. Example: Ticket created Date is 7/05/2014 12:00:00 AM and the Due Date should have 31/05/2014 11:59:59 PM. it should automatically calculate or populate the Month End Date in Due Date field. I have used this condition ($DATE$ + 43200) + (86400 * 31)) - (86400 * DAY((($DATE$ + 43200) + (31 * 86400) - (3600 * HOUR((($DATE$ + 43200) + (31 * 86400) + (DAY($DATE$) * 86400)) - 1, Which given me the Due Date as 7/06/2014 11:59:00 PM. Can any one help me on this, Please. _ARSlist: Where the Answers Are and have been for 20 years_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years -- This message was scanned by ESVA and is believed to be clean. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
Re: Set Month End Date
No. Look at Action 1 again. It starts the calculations with the 1st of the month. I was just trying to illustrate the logic behind the calculation. As Misi points out (in another post) it can be performed in a single Set Fields action Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of pritch Sent: Wednesday, May 07, 2014 4:34 PM To: arslist@ARSLIST.ORG Subject: Re: Set Month End Date Wouldn't the calculation of 32 days cause it to possibly jump over a month? ie if the ticket is created on March 31 (or even March 30) it would calcuate a date in May. Rather than try to play with the days - could we do something like this? Set Fields actions (in Filter or Active Link) Action 1:$ztmp datetime field$ = DATEADD(mm, 1, $DATE$) Action 2:$ztmp datetime field$ = MONTH($ztmp datetime field$) + /1/ + YEAR($ztmp datetime field) Action 3:$ztmp datetime field$ = $ztmp datetime field$ - 1 - Original Message - From: Frederick W Grooms frederick.w.gro...@xo.com To: arslist@ARSLIST.ORG Sent: Wednesday, May 7, 2014 11:35:12 AM Subject: Re: Set Month End Date This is the way I have done it in the past (Note: I am using US date format so convert as needed) Set Fields actions (in Filter or Active Link) Action 1:$ztmp string field$ = MONTH($DATE$) + /1/ + YEAR($DATE) Action 2:$ztmp datetime field$ = $ztmp string field$ Action 3:$ztmp datetime field$ = $ztmp datetime field$ + (32 * 86400) Action 4:$ztmp string field$ = MONTH($ztmp datetime field$) + /1/ + YEAR($ztmp datetime field) Action 5:$ztmp datetime field$ = $ztmp string field$ Action 6:$ztmp datetime field$ = $ztmp datetime field$ - 1 At this point the display only datetime field has 11:59:59 PM of the last day of the month I broke the example out into individual set fields actions so you can see exactly what is happening. Here is what each action is doing: Action 1: Create a string of the first day of the current month Action 2: Put the string into a date field Action 3: Add enough days to make the temp field the next month Action 4: Create a string of the first day of the month of the temp date field Action 5: Put the string into a date field Action 6: Subtract 1 second from the temporary date field to get the last day of the previous month Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Terje Moglestue Sent: Wednesday, May 07, 2014 7:37 AM To: arslist@ARSLIST.ORG Subject: Re: Set Month End Date ** Karthick, It is years since I have done this type of calculations. In your example I would find the first day of the next month and then just take minus one day. The outcome would the last day of the month you are in. ~ Terje -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Karthick S Sent: Wednesday, May 07, 2014 8:50 AM To: arslist@ARSLIST.ORG Subject: Set Month End Date ** I need to Set a Month End Date in a field Called Due Date. Please find the below. Example: Ticket created Date is 7/05/2014 12:00:00 AM and the Due Date should have 31/05/2014 11:59:59 PM. it should automatically calculate or populate the Month End Date in Due Date field. I have used this condition ($DATE$ + 43200) + (86400 * 31)) - (86400 * DAY((($DATE$ + 43200) + (31 * 86400) - (3600 * HOUR((($DATE$ + 43200) + (31 * 86400) + (DAY($DATE$) * 86400)) - 1, Which given me the Due Date as 7/06/2014 11:59:00 PM. Can any one help me on this, Please. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
Re: Set Month End Date
Wouldn't the calculation of 32 days cause it to possibly jump over a month? ie if the ticket is created on March 31 (or even March 30) it would calcuate a date in May. Rather than try to play with the days - could we do something like this? Set Fields actions (in Filter or Active Link) Action 1:$ztmp datetime field$ = DATEADD(mm, 1, $DATE$) Action 2:$ztmp datetime field$ = MONTH($ztmp datetime field$) + /1/ + YEAR($ztmp datetime field) Action 3:$ztmp datetime field$ = $ztmp datetime field$ - 1 - Original Message - From: Frederick W Grooms frederick.w.gro...@xo.com To: arslist@ARSLIST.ORG Sent: Wednesday, May 7, 2014 11:35:12 AM Subject: Re: Set Month End Date This is the way I have done it in the past (Note: I am using US date format so convert as needed) Set Fields actions (in Filter or Active Link) Action 1:$ztmp string field$ = MONTH($DATE$) + /1/ + YEAR($DATE) Action 2:$ztmp datetime field$ = $ztmp string field$ Action 3:$ztmp datetime field$ = $ztmp datetime field$ + (32 * 86400) Action 4:$ztmp string field$ = MONTH($ztmp datetime field$) + /1/ + YEAR($ztmp datetime field) Action 5:$ztmp datetime field$ = $ztmp string field$ Action 6:$ztmp datetime field$ = $ztmp datetime field$ - 1 At this point the display only datetime field has 11:59:59 PM of the last day of the month I broke the example out into individual set fields actions so you can see exactly what is happening. Here is what each action is doing: Action 1: Create a string of the first day of the current month Action 2: Put the string into a date field Action 3: Add enough days to make the temp field the next month Action 4: Create a string of the first day of the month of the temp date field Action 5: Put the string into a date field Action 6: Subtract 1 second from the temporary date field to get the last day of the previous month Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Terje Moglestue Sent: Wednesday, May 07, 2014 7:37 AM To: arslist@ARSLIST.ORG Subject: Re: Set Month End Date ** Karthick, It is years since I have done this type of calculations. In your example I would find the first day of the next month and then just take minus one day. The outcome would the last day of the month you are in. ~ Terje -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Karthick S Sent: Wednesday, May 07, 2014 8:50 AM To: arslist@ARSLIST.ORG Subject: Set Month End Date ** I need to Set a Month End Date in a field Called Due Date. Please find the below. Example: Ticket created Date is 7/05/2014 12:00:00 AM and the Due Date should have 31/05/2014 11:59:59 PM. it should automatically calculate or populate the Month End Date in Due Date field. I have used this condition ($DATE$ + 43200) + (86400 * 31)) - (86400 * DAY((($DATE$ + 43200) + (31 * 86400) - (3600 * HOUR((($DATE$ + 43200) + (31 * 86400) + (DAY($DATE$) * 86400)) - 1, Which given me the Due Date as 7/06/2014 11:59:00 PM. Can any one help me on this, Please. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
Re: Set Month End Date
The DateNum function is your friend here, try this Have a temp character field and set it to be: Temp Character = (DATENUM(mm, $DATE$) +1) + /1/) + YEAR($TIMESTAMP$) (You may have to reformat this a bit depending on Locale) Then have another set fields into a temp date field that does Temp Date = $Temp Character$ Then one more that does DueDate = $Temp Date$ - 1 I haven't tested this but it should work From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Karthick S Sent: Wednesday, May 07, 2014 3:50 AM To: arslist@ARSLIST.ORG Subject: Set Month End Date ** I need to Set a Month End Date in a field Called Due Date. Please find the below. Example: Ticket created Date is 7/05/2014 12:00:00 AM and the Due Date should have 31/05/2014 11:59:59 PM. it should automatically calculate or populate the Month End Date in Due Date field. I have used this condition ($DATE$ + 43200) + (86400 * 31)) - (86400 * DAY((($DATE$ + 43200) + (31 * 86400) - (3600 * HOUR((($DATE$ + 43200) + (31 * 86400) + (DAY($DATE$) * 86400)) - 1, Which given me the Due Date as 7/06/2014 11:59:00 PM. Can any one help me on this, Please. _ARSlist: Where the Answers Are and have been for 20 years_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
Re: Set Month End Date
This is the way I have done it in the past (Note: I am using US date format so convert as needed) Set Fields actions (in Filter or Active Link) Action 1:$ztmp string field$ = MONTH($DATE$) + /1/ + YEAR($DATE) Action 2:$ztmp datetime field$ = $ztmp string field$ Action 3:$ztmp datetime field$ = $ztmp datetime field$ + (32 * 86400) Action 4:$ztmp string field$ = MONTH($ztmp datetime field$) + /1/ + YEAR($ztmp datetime field) Action 5:$ztmp datetime field$ = $ztmp string field$ Action 6:$ztmp datetime field$ = $ztmp datetime field$ - 1 At this point the display only datetime field has 11:59:59 PM of the last day of the month I broke the example out into individual set fields actions so you can see exactly what is happening. Here is what each action is doing: Action 1: Create a string of the first day of the current month Action 2: Put the string into a date field Action 3: Add enough days to make the temp field the next month Action 4: Create a string of the first day of the month of the temp date field Action 5: Put the string into a date field Action 6: Subtract 1 second from the temporary date field to get the last day of the previous month Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Terje Moglestue Sent: Wednesday, May 07, 2014 7:37 AM To: arslist@ARSLIST.ORG Subject: Re: Set Month End Date ** Karthick, It is years since I have done this type of calculations. In your example I would find the first day of the next month and then just take minus one day. The outcome would the last day of the month you are in. ~ Terje -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Karthick S Sent: Wednesday, May 07, 2014 8:50 AM To: arslist@ARSLIST.ORG Subject: Set Month End Date ** I need to Set a Month End Date in a field Called Due Date. Please find the below. Example: Ticket created Date is 7/05/2014 12:00:00 AM and the Due Date should have 31/05/2014 11:59:59 PM. it should automatically calculate or populate the Month End Date in Due Date field. I have used this condition ($DATE$ + 43200) + (86400 * 31)) - (86400 * DAY((($DATE$ + 43200) + (31 * 86400) - (3600 * HOUR((($DATE$ + 43200) + (31 * 86400) + (DAY($DATE$) * 86400)) - 1, Which given me the Due Date as 7/06/2014 11:59:00 PM. Can any one help me on this, Please. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
Re: Set Month End Date
Try this Action 1 sets the date to the first of next month Action 2 subtracts 1 minute Action 1: Set Fields Field : Due Date Value: DATEADD(mm, 1, DATEADD(dd, -(DAY($Create Date$) - 1), $Create Date$)) Action 2: Set Fields Field: Due Date Value: $Due Date$ - 60 Greg Givens -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Karthick S Sent: Wednesday, May 07, 2014 2:50 AM To: arslist@ARSLIST.ORG Subject: Set Month End Date ** I need to Set a Month End Date in a field Called Due Date. Please find the below. Example: Ticket created Date is 7/05/2014 12:00:00 AM and the Due Date should have 31/05/2014 11:59:59 PM. it should automatically calculate or populate the Month End Date in Due Date field. I have used this condition ($DATE$ + 43200) + (86400 * 31)) - (86400 * DAY((($DATE$ + 43200) + (31 * 86400) - (3600 * HOUR((($DATE$ + 43200) + (31 * 86400) + (DAY($DATE$) * 86400)) - 1, Which given me the Due Date as 7/06/2014 11:59:00 PM. Can any one help me on this, Please. _ARSlist: Where the Answers Are and have been for 20 years_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years smime.p7s Description: S/MIME cryptographic signature
Re: Set Month End Date
Karthick, It is years since I have done this type of calculations. In your example I would find the first day of the next month and then just take minus one day. The outcome would the last day of the month you are in. ~ Terje From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Karthick S Sent: Wednesday, May 07, 2014 8:50 AM To: arslist@ARSLIST.ORG Subject: Set Month End Date ** I need to Set a Month End Date in a field Called Due Date. Please find the below. Example: Ticket created Date is 7/05/2014 12:00:00 AM and the Due Date should have 31/05/2014 11:59:59 PM. it should automatically calculate or populate the Month End Date in Due Date field. I have used this condition ($DATE$ + 43200) + (86400 * 31)) - (86400 * DAY((($DATE$ + 43200) + (31 * 86400) - (3600 * HOUR((($DATE$ + 43200) + (31 * 86400) + (DAY($DATE$) * 86400)) - 1, Which given me the Due Date as 7/06/2014 11:59:00 PM. Can any one help me on this, Please. _ARSlist: Where the Answers Are and have been for 20 years_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
Set Month End Date
I need to Set a Month End Date in a field Called Due Date. Please find the below. Example: Ticket created Date is 7/05/2014 12:00:00 AM and the Due Date should have 31/05/2014 11:59:59 PM. it should automatically calculate or populate the Month End Date in Due Date field. I have used this condition ($DATE$ + 43200) + (86400 * 31)) - (86400 * DAY((($DATE$ + 43200) + (31 * 86400) - (3600 * HOUR((($DATE$ + 43200) + (31 * 86400) + (DAY($DATE$) * 86400)) - 1, Which given me the Due Date as 7/06/2014 11:59:00 PM. Can any one help me on this, Please. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Where the Answers Are, and have been for 20 years
