Formatting date entries


#1

Hullo - I have just become a member and I would appreciate help in formatting date entries which I have placed in the COMMENTS field.
These entries are at present written as numbers e.g. 20090415 (representing 15 April 2009).
Is there a way of formatting these entries as 2009 April 14 ?
I have searched the forum and FAQs and have not as yet found an answer.
I have found the program to be very helpful, thankyou. Tonyorob.


Extract weekdays and month names from timestamp
#2

One day befor? sure?
If you mean 2009 April 15 try this:

New action

Format value
Field: COMMENT
Format string:
$replace(%comment%,$mid(%comment%,5,2),$if($eql($mid(%comment%,5,2),01), January ,$if($eql($mid(%comment%,5,2),02), February ,$if($eql($mid(%comment%,5,2),03), March ,$if($eql($mid(%comment%,5,2),04), April ,$if($eql($mid(%comment%,5,2),05), May ,$if($eql($mid(%comment%,5,2),06), June ,$if($eql($mid(%comment%,5,2),07), July ,$if($eql($mid(%comment%,5,2),08), August ,$if($eql($mid(%comment%,5,2),09), September ,$if($eql($mid(%comment%,5,2),10), October ,$if($eql($mid(%comment%,5,2),11), November ,$if($eql($mid(%comment%,5,2),12), December ,$mid(%comment%,5,2))))))))))))))


#3

Full names of the months (one line of code):

$left(%COMMENT%,4)' ' $replace($mid(%COMMENT%,5,2), '01','January','02','February','03','March','04','April','05','May','06','June', '07','July','08','August','09','September','10','October','11','November','12','December') ' '$right(%COMMENT%,2)

Abbreviated names of the months (one line of code):

$left(%COMMENT%,4)' ' $replace($mid(%COMMENT%,5,2), '01','Jan','02','Feb','03','Mar','04','Apr','05','May','06','June', '07','July','08','Aug','09','Sept','10','Oct','11','Nov','12','Dec') ' '$right(%COMMENT%,2)

Use formula within action:

Actiontype 5: Format tag field
Field: COMMENT
Formatstring: $left(%comment%,4)'÷'$replace($mid(%comment%,5,2),
'01','January','02','February','03','March','04','April','05','May','06','June',
'07','July','08','August','09','September','10','October','11','November','12','December')
'÷'$right(%comment%,2)

Actiontype 5: Format tag field
Field: COMMENT
Formatstring: $left(%comment%,4)'÷'$replace($mid(%comment%,5,2),
'01','Jan','02','Feb','03','Mar','04','Apr','05','May','06','June',
'07','July','08','Aug','09','Sept','10','Oct','11','Nov','12','Dec')
'÷'$right(%comment%,2)

Note: Replace one special character ÷ with one space character.

If you want to subtract one day and have it formatted as two digits, then use this formula:

$right('00'$sub($right(%COMMENT%,2),1),2)

DD.20090415.1710.CEST


#4

Newser
Thanks so much for your help. I did mean the same day, not the day before - sorry!
The code string you sent me works well on most date numbers but where the year month or day have identical 2 figures the result shows an error. I quote some examples below.

20060607 = 20 June June 07 (06)
20070729 = 20 July July 29 (07)
20070909 = 2007 September September (09)
20071111 = 2007 November November (11)
20080824 = 20 August August 24 (08)

Othewise the code string works well. Perhaps you can modify this to correct the problem.

Thanks again

Anthony Robertson :rolleyes:


#5

At the moment i don't know why there's an error.
But you can use DelevD's expert problem solving. It works perfect.


#6

Thank you again to Newser and DetlevD for your input.
I have used DetlevD's string and it works well with year and month, but does not include the day number.
I have a friend who has also shown interest and he has given me this code-string which works well for year, month and day also.

$left(%comment%,4) $if($eql($mid(%comment%,5, 2),01),January,$if($eql($mid(%comment%,5,2),02),February,$if($eql($mid(%comment%,5,2),03),March,$if($eql($mid(%comment%,5,2),04),April,$if($eql($mid(%comment%,5,2),05),May,$if($eql($mid(%comment%,5,2),06),June,$if($eql($mid(%comment%,5,2),07),July,$if($eql($mid(%comment%,5,2),08),August,$if($eql($mid(%comment%,5,2),09),September,$if($eql($mid(%comment%,5,2),10),October,$if($eql($mid(%comment%,5,2),11),November,$if($eql($mid(%comment%,5,2),12),December,$mid(%comment%, 5, 2))))))))))))) $right(%comment%,2)

I think my problem is now solved.
Thanks again
Anthony Robertson :music:


#7

Hi newser,
the problem with this formula is, that it replaces all targets within the given input source string.
Condensed example:

comment = '20080815'
$mid(%comment%,5,2) = '08'
$replace('20080815','08',' August ') ==> '20 August  August 15'

DD.20090416.1320.CEST


#8

There are many ways to Rome.
Look again carefully ... my path also leads to a proper solution with smaller code.

Hi, newser, thank you for verifying.

DD.20090416.1332.CEST