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
aveenm293
Offline
Last seen: 4 years 11 months ago
Joined: 10/23/2012 - 16:02
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.
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
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.
Cool, Thank you!
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,
Are you talking about having a script that will create a virtual field with that information or just a script where you can input the information and get the result?
The Latter
Ok, that shouldn't be a problem. I will put one together and post it for you.
Great! Thanks a lot Brian!
:-)
Avi
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.
Pages