Script to change dates
Forums
Hi Brian
I have another request. I usually work with data that contains numerous dates which are all in character format. I generally use the @cTod and this always works. Recently I have received with data with the date formats as follows:
1 February 2013
2003 February 1
1 Mar 2013
Mar 1, 2013
Is is possible to write a script that will change any data like above to the format YYYYMMDD.
Thank you
Regards
Aveen
Hi Brian
Hi Brian
I tried the script you directed me too and it works really well. I can across some weird looking dates. I have attached a spreadsheet. The date in the first column shows the date as normal. However in the second column the date is represented as a bunch of numbers. Of course if you change the format in excel to date the numbers appear as the date in the first column. Is there a way in idea to change these numbers into a date.
Thank you
Regards
Aveen
Days to date function
Hi Aveen, glad that the custom function worked. For the numbers in your spreadsheet you can use the @DaysToD(Numeric Field) to translate them into a date field. So just create a virtual field and use this function and you should be good to go for dates stored numerically.
hi Brian
Hi Brian
I would like to do some date calculations in idea without using the equation editor. for example I would like to do the equivalent of @DaystoD(@DtoDays(<some date>) + <some numeric value >)
is there a simple way to do this date arithmetic in IDEA scripts, or alternatively, is there a shell application or excel application object that can be used to do something equivalent?
THANKS
Hi Avi, do you want to try
Hi Avi, do you want to try this out and let me know if this is what you are looking for. I didn't put in any error checking so it will give you errors if you don't enter a proper date or put in characters. I will probably do that once I see if this is what you are looking for.
Hi Aveen, can you try this
Hi Aveen, can you try this custom function as it will handle the example you have. You can find it here: http://ideascripting.com/content/changing-character-date-field-date-field It uses the CDate function in Visual Basic which is quite powerful and can translate most date formats.