Try this [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000009#000000"]thread[/url] from last year. If there's too much to plod through - - after all, it's one of mine - - just go for the relevant part of the topic, namely:
Because of the mm/dd/yy format, use a modification of the dd/mmm/yy formula used in my original tip:
This should give you the “real” date of 07/01/02. The messy substring formula takes any text date of "mm/dd/yy" and changes it to a text date of "mm/01/yy," then Ctod changes it to a “real” date of mm/01/yy.[/quote]Now, let us know if have anything else to use your date for - - the possibilities are endless!
Best of luck!
[size="1"][ May 01, 2006, 05:39 PM: Message edited by: Todd Niemi ][/size]
Just revisited this as it came to the top of the list and I suddenly remembered Winn's solution to the [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000197#000009"]'back one month problem'[/url] from May this year.
That was a little more complex than this requirement as it was a month shift and the number of days in a month became significant.
Here it would be a bit easier. Haven't tried it but it seems to me that it should be possible to get the date day ( say the 7th of the month) and simply subtract the (number of days -1) that gives from the reported date, thus giving the 1st of the month. Something similar could be done with the month calculation.
Any comments? Looks like 2 solutions for the price of one but I may have missed something.
[size="1"][ May 01, 2006, 05:40 PM: Message edited by: Todd Niemi ][/size]