Getting Date/Time variable from text?

[terrypin]

I have date strings like 03/04/09 in a text variable and want to display that as Friday 3 April 2009. But I can't use use ME Pro's attractive formatting repertoire, because incredibly there's no command for the crucial first step of getting such text for Day, Month and Year into a Date/Time variable. Or have I missed something here?

 

I understand from Cory's comments in the thread http://pgmacros.invisionzone.com/index.php?showtopic=3706 that this is a long-standing major flaw in ME Pro's Date/Time commands. If so, can ISS tell us if this is due to be fixed please?

 

Meanwhile has ISS or anyone here worked through the logic and maths to derive the 'decimal date equivalent', using Day, Month & Year please? Or know what Excel's algorithm is for this, as I assume the values are identical?

 

Of course, I could load Excel every time I want to do such a format conversion, but that truly would be a top heavy approach!

 

--

Terry, East Grinstead, UK

[acantor]

Sometimes a macro that does part of the job is better than not having a macro at all!

 

Here is a partial solution. It does not calculate the day of the week -- a task I leave to greater minds. But converting dd/mm/yy to d mmmm yyyy was straightforward. It took only an hour or so to get it to work, including its rudimentary validation checks.

 

This macro could be easily be modified to type out the date, and then move the cursor back to where you want the day of the week inserted!

 

// Convert DD/MM/YY to D MMMM YYYY
// NumericDate = Date as DD/MM/YY
Variable Set String %NumericDate%: Prompt
// Validation 1: Is NumericDate eight characters long? If not, quit.
Variable Set Integer %StringLength% to the length of variable %NumericDate%
If Variable %StringLength% Does not Equal "8"
 Text Box Display: Validation problem: Wrong number of characters
 Macro Stop
End If
// Validation 2: Check for / in positions 3 and 6. If one or both are missing, quit.
Variable Set String %FindForwardSlash% to "%NumericDate%"
Variable Modify String %FindForwardSlash%: Delete a substring starting at 1 and 2 characters long
Variable Modify String %FindForwardSlash%: Delete a substring starting at 2 and 2 characters long
Variable Modify String %FindForwardSlash%: Delete a substring starting at 3 and 2 characters long
If Variable %FindForwardSlash% Does not Equal "//"
 Text Box Display: Validation problem with slashes
 Macro Stop
End If
// Split NumericDate in three with / as separator
Split String "%NumericDate%" on "/" into %DatePiece%, starting at 1
// DatePiece[1] = Day of Week
// If it starts with a zero, remove the zero. E.g., "09" becomes "9"
If Variable %DatePiece[1]% Is Less Than "9"
 Variable Modify String %DatePiece[1]%: Delete a substring starting at 1 and 1 characters long
End If
// Change numeric month to alphabetical month...
// DatePiece[2] = Month
Switch( %DatePiece[2]% )
Case: 01
 Variable Set String %DatePiece[2]% to "January"
End Case
Case: 02
 Variable Set String %DatePiece[2]% to "February"
End Case
Case: 03
 Variable Set String %DatePiece[2]% to "March"
End Case
Case: 04
 Variable Set String %DatePiece[2]% to "April"
End Case
Case: 05
 Variable Set String %DatePiece[2]% to "May"
End Case
Case: 06
 Variable Set String %DatePiece[2]% to "June"
End Case
Case: 07
 Variable Set String %DatePiece[2]% to "July"
End Case
Case: 08
 Variable Set String %DatePiece[2]% to "August"
End Case
Case: 09
 Variable Set String %DatePiece[2]% to "September"
Case: 10
 Variable Set String %DatePiece[2]% to "October"
End Case
Case: 11
 Variable Set String %DatePiece[2]% to "November"
End Case
Case: 12
 Variable Set String %DatePiece[2]% to "December"
End Case
Default Case
// Validation 3: Month must be 01 - 12
 Text Box Display: Validation problem: Month is incorrect.
 Macro Stop
End Case
End Switch
// DatePiece[3] = Year
// For 00 - 50, assume 21st century
// For 51 - 99, assume 20th century
If Variable %DatePiece[3]% Is Less Than "50"
 Variable Set String %DatePiece[3]% to "20%DatePiece[3]%"
Else
 Variable Set String %DatePiece[3]% to "19%DatePiece[3]%"
End If
Text Box Display: Result

 

<COMMENT Value="Convert DD/MM/YY to D MMMM YYYY"/>
<COMMENT Value="NumericDate = Date as DD/MM/YY"/>
<VARIABLE SET STRING Option="\x01" Destination="%NumericDate%" Prompt="Enter date as DD/MM/YY" Mask="FALSE" OnTop="FALSE" Left="Center" Top="Center" Monitor="0"/>
<COMMENT Value="Validation 1: Is NumericDate eight characters long? If not, quit."/>
<VARIABLE SET INTEGER Option="\x0D" Destination="%StringLength%" Text_Variable="%NumericDate%"/>
<IF VARIABLE Variable="%StringLength%" Condition="\x01" Value="8" IgnoreCase="FALSE"/>
<TEXT BOX DISPLAY Title="Validation problem: Wrong number of characters" Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang4105{\\fonttbl{\\f0\\fnil\\fcharset0 Tahoma;}{\\f1\\fnil Tahoma;}}\r\n\\viewkind4\\uc1\\pard\\f0\\fs16 Date must be in this format: dd/mm/yy\\f1 \r\n\\par }\r\n" Left="Center" Top="Center" Width="278" Height="200" Monitor="0" OnTop="TRUE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>
<MACRO STOP/>
<END IF/>
<COMMENT Value="Validation 2: Check for / in positions 3 and 6. If one or both are missing, quit."/>
<VARIABLE SET STRING Option="\x00" Destination="%FindForwardSlash%" Value="%NumericDate%"/>
<VARIABLE MODIFY STRING Option="\x0A" Destination="%FindForwardSlash%" Start="1" Count="2"/>
<VARIABLE MODIFY STRING Option="\x0A" Destination="%FindForwardSlash%" Start="2" Count="2"/>
<VARIABLE MODIFY STRING Option="\x0A" Destination="%FindForwardSlash%" Start="3" Count="2"/>
<IF VARIABLE Variable="%FindForwardSlash%" Condition="\x01" Value="//" IgnoreCase="FALSE"/>
<TEXT BOX DISPLAY Title="Validation problem with slashes" Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang4105{\\fonttbl{\\f0\\fnil\\fcharset0 Tahoma;}{\\f1\\fnil Tahoma;}}\r\n\\viewkind4\\uc1\\pard\\f0\\fs16 Date must be in this format: \r\n\\par \r\n\\par dd/mm/yy\\f1 \r\n\\par }\r\n" Left="Center" Top="Center" Width="278" Height="200" Monitor="0" OnTop="TRUE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>
<MACRO STOP/>
<END IF/>
<COMMENT Value="Split NumericDate in three with / as separator"/>
<SPLIT STRING Source="%NumericDate%" SplitChar="/" Dest="%DatePiece%" Index="1"/>
<COMMENT Value="DatePiece[1] = Day of Week"/>
<COMMENT Value="If it starts with a zero, remove the zero. E.g., \"09\" becomes \"9\""/>
<IF VARIABLE Variable="%DatePiece[1]%" Condition="\x02" Value="9" IgnoreCase="FALSE"/>
<VARIABLE MODIFY STRING Option="\x0A" Destination="%DatePiece[1]%" Start="1" Count="1"/>
<END IF/>
<COMMENT Value="Change numeric month to alphabetical month..."/>
<COMMENT Value="DatePiece[2] = Month"/>
<SWITCH Variable="%DatePiece[2]%"/>
<CASE Value="01"/>
<VARIABLE SET STRING Option="\x00" Destination="%DatePiece[2]%" Value="January"/>
<END CASE/>
<CASE Value="02"/>
<VARIABLE SET STRING Option="\x00" Destination="%DatePiece[2]%" Value="February"/>
<END CASE/>
<CASE Value="03"/>
<VARIABLE SET STRING Option="\x00" Destination="%DatePiece[2]%" Value="March"/>
<END CASE/>
<CASE Value="04"/>
<VARIABLE SET STRING Option="\x00" Destination="%DatePiece[2]%" Value="April"/>
<END CASE/>
<CASE Value="05"/>
<VARIABLE SET STRING Option="\x00" Destination="%DatePiece[2]%" Value="May"/>
<END CASE/>
<CASE Value="06"/>
<VARIABLE SET STRING Option="\x00" Destination="%DatePiece[2]%" Value="June"/>
<END CASE/>
<CASE Value="07"/>
<VARIABLE SET STRING Option="\x00" Destination="%DatePiece[2]%" Value="July"/>
<END CASE/>
<CASE Value="08"/>
<VARIABLE SET STRING Option="\x00" Destination="%DatePiece[2]%" Value="August"/>
<END CASE/>
<CASE Value="09"/>
<VARIABLE SET STRING Option="\x00" Destination="%DatePiece[2]%" Value="September"/>
<CASE Value="10"/>
<VARIABLE SET STRING Option="\x00" Destination="%DatePiece[2]%" Value="October"/>
<END CASE/>
<CASE Value="11"/>
<VARIABLE SET STRING Option="\x00" Destination="%DatePiece[2]%" Value="November"/>
<END CASE/>
<CASE Value="12"/>
<VARIABLE SET STRING Option="\x00" Destination="%DatePiece[2]%" Value="December"/>
<END CASE/>
<DEFAULT CASE/>
<COMMENT Value="Validation 3: Month must be 01 - 12"/>
<TEXT BOX DISPLAY Title="Validation problem: Month is incorrect." Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang4105{\\fonttbl{\\f0\\fnil Tahoma;}}\r\n\\viewkind4\\uc1\\pard\\f0\\fs16 Month %DatePiece[2]% must be between 1 and 12\r\n\\par }\r\n" Left="Center" Top="Center" Width="278" Height="200" Monitor="0" OnTop="TRUE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>
<MACRO STOP/>
<END CASE/>
<END SWITCH/>
<COMMENT Value="DatePiece[3] = Year"/>
<COMMENT Value="For 00 - 50, assume 21st century"/>
<COMMENT Value="For 51 - 99, assume 20th century"/>
<IF VARIABLE Variable="%DatePiece[3]%" Condition="\x02" Value="50" IgnoreCase="FALSE"/>
<VARIABLE SET STRING Option="\x00" Destination="%DatePiece[3]%" Value="20%DatePiece[3]%"/>
<ELSE/>
<VARIABLE SET STRING Option="\x00" Destination="%DatePiece[3]%" Value="19%DatePiece[3]%"/>
<END IF/>
<TEXT BOX DISPLAY Title="Result" Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang4105{\\fonttbl{\\f0\\fnil\\fcharset0 Tahoma;}{\\f1\\fnil Tahoma;}}\r\n\\viewkind4\\uc1\\pard\\f0\\fs16 %DatePiece[1]% %DatePiece[2]% %DatePiece[3]%\\f1 \r\n\\par }\r\n" Left="Center" Top="Center" Width="278" Height="200" Monitor="0" OnTop="FALSE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>

[terrypin]

Thanks Alan, appreciate the time and effort! That's a good chunk of it done, all I need now is dddd I'll do some research and see if I can come up with a formula for that.

 

--

Terry, East Grinstead, UK

[Cory]

Meanwhile has ISS or anyone here worked through the logic and maths to derive the 'decimal date equivalent', using Day, Month & Year please?

I wrote a macro for calculating day of the year some time ago and posted it on my website and a couple of months ago I wrote an algorithm for calculating serial dates including decimal time values. I deferred writing the actual code because I had another project that took precedence and I was hoping that ISS might add the functionality before I really needed it but I have one macro now that's moving higher on the priority list that needs it. I could be persuaded to actually write the rest of the script for the serial date conversion if someone else had a need. Is this the case?

[terrypin]

I wrote a macro for calculating day of the year some time ago and posted it on my website and a couple of months ago I wrote an algorithm for calculating serial dates including decimal time values. I deferred writing the actual code because I had another project that took precedence and I was hoping that ISS might add the functionality before I really needed it but I have one macro now that's moving higher on the priority list that needs it. I could be persuaded to actually write the rest of the script for the serial date conversion if someone else had a need. Is this the case?

 

Thanks Cory. I'd be very interested in the algorithm for determining the decimal value. I too would have hoped ISS might have turned it into a macro by now...

 

Meanwhile, to complete Alan's code, it would be good to have a macro (or algorithm) for day of the week (not year). For example, given D = 3, M =4, Y = 2009, then Dw = 'Friday'.

 

--

Terry, East Grinstead, UK

[Cory]

It's not that simple. In effect my need is for a day of the week. Actually my need is to be able to count back X number of workdays. For day of the week one simply takes the serial date number, divide by seven and take the remainder. I discussed it in another post not to long ago. You can search for it. But from a string date it gets complicated.

 

Now mind you if you would like to do some limited date range calculations you can do it in a sleazy manner. Check out my macro on my website for calculating the day of year. As long as we're just talking about dates within a year or two from now that can be adapted for your needs. EG if you are calculating a day in 2009 you can take that to calculate the day number in 2009 and then add whatever 12/31/2008's serial date was to that.

 

My algorithm actually will work for any time in history or future. It has a three stage calculation that applies the 4, 100, and 400 year leap day rules. It's pretty cool. I might work on it today or this weekend.

[acantor]

I found several algorithms on line for calculating the day of the week for any date. For fun, I tried to implement one of solutions in Macro Express. It's challenging -- I am not a professional programmer, there are many variables to keep track of, and there a number of special cases that need to be dealt with -- but I think the algorithm is do-able.

 

I will post my solution if I succeed.

 

But one aspect of the coding is a little beyond me. Can someone help?

 

Let's say there are two decimal variables, %D1% and %D2%. In Macro Express, how do I code %D1% mod %D2%? I have not studied math in over 30 years, and my recollection is that mod has to do with the remainder? Or the numbers after the decimal point?

[terrypin]

I found several algorithms on line for calculating the day of the week for any date. For fun, I tried to implement one of solutions in Macro Express. It's challenging -- I am not a professional programmer, there are many variables to keep track of, and there a number of special cases that need to be dealt with -- but I think the algorithm is do-able.

 

I will post my solution if I succeed.

 

But one aspect of the coding is a little beyond me. Can someone help?

 

Let's say there are two decimal variables, %D1% and %D2%. In Macro Express, how do I code %D1% mod %D2%? I have not studied math in over 30 years, and my recollection is that mod has to do with the remainder? Or the numbers after the decimal point?

 

I'm guessing you found Zeller's Congruence as I did, perhaps at http://en.wikipedia.org/wiki/Zeller%27s_congruence ?

 

It seems ME Pro has no direct MOD command so (after first converting decimal to integer if necessary) you could do it with the three Variable Modify commands I used in this simple demo:

 

Repeat Until %T[1]% Does not Equal "%T[1]%" // Repeat indefinitely

(User will stop the macro manually)

Variable Set Integer %N[1]%: Prompt // Choose a number

Variable Modify Integer: %N[2]% = %N[1]% / 7 // Divide it by 7, taking the whole number result N2

Variable Modify Integer: %N[3]% = %N[2]% * 7 // Multiply N2 by 7 to get an intermediate result N3

Variable Modify Integer: MOD = %N[1]% - %N[3]% // Display results

Text Box Display: Results

End Repeat

 

<REPEAT UNTIL Variable="%T[1]%" Condition="\x01" Value="%T[1]%" _COMMENT="Repeat indefinitely\r\n(User will stop the macro manually)"/>
<VARIABLE SET INTEGER Option="\x01" Destination="%N[1]%" Prompt="Enter any number" Mask="FALSE" OnTop="FALSE" Left="Center" Top="Center" Monitor="0" _COMMENT="Choose a number"/>
<VARIABLE MODIFY INTEGER Option="\x03" Destination="%N[2]%" Value1="%N[1]%" Value2="7" _COMMENT="Divide it by 7, taking the whole number result N2"/>
<VARIABLE MODIFY INTEGER Option="\x02" Destination="%N[3]%" Value1="%N[2]%" Value2="7" _COMMENT="Multiply N2 by 7 to get an intermediate result N3"/>
<VARIABLE MODIFY INTEGER Option="\x01" Destination="MOD" Value1="%N[1]%" Value2="%N[3]%" _COMMENT="Display results"/>
<TEXT BOX DISPLAY Title="Results" Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang2057{\\fonttbl{\\f0\\fnil\\fcharset0 Tahoma;}{\\f1\\fnil Tahoma;}}\r\n{\\colortbl ;\\red255\\green0\\blue0;}\r\n\\viewkind4\\uc1\\pard\\qc\\f0\\fs16 N1 = %N[1]%\r\n\\par N2 = %N[2]%\r\n\\par N3 = %N[3]%\r\n\\par \r\n\\par \\cf1\\b MOD(N1,7) = %MOD%\\cf0\\b0\\f1 \r\n\\par }\r\n" Left="Center" Top="Center" Width="233" Height="200" Monitor="0" OnTop="FALSE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>
<END REPEAT/>

 

Then you'd convert as appropriate, e.g. 0 = Sunday, 1 = Monday, etc.

 

I'm off for a week's walking holiday in Portugal in a few hours. Look forward to seeing your solution on my return in a week or so!

 

--

Terry, East Grinstead, UK

[paul]

Terry, I thought you had purchased the PGM Functions Library? If so, there are date functions contained within which should meet your needs.

[Cory]

Paul,

 

I was not aware of a PGM function to return serial date value from text. Does such an animal exist?

[Cory]

Here is my new Text to Serial Time Converter. If it works for you maybe consider making a donation to me:)

[paul]

I was not aware of a PGM function to return serial date value from text. Does such an animal exist?

Here's a 3-line macro (ignoring the last line, and you need either line 1 OR line 2) that I think does what you want (I'm using today's date as my example):

<DATE/TIME Format="dd mmm yyyy" Flags="\xB0" Date="5/04/2009 10:22:45" Day_Offset="0" Month_Offset="0" Year_Offset="0" Hour_Offset="0" Minute_Offset="0" Second_Offset="0" Left="Center" Top="Center" Monitor="0" Variable="%Date1%" IsDateVar="FALSE"/>
  <DATE/TIME Format="dd mmmm yyyy" Flags="\xB0" Date="5/04/2009 10:22:45" Day_Offset="0" Month_Offset="0" Year_Offset="0" Hour_Offset="0" Minute_Offset="0" Second_Offset="0" Left="Center" Top="Center" Monitor="0" Variable="%Date2%" IsDateVar="FALSE"/>
  <MACRO RUN Use_ID="FALSE" Name="{ DateTime - Date to Character DOW }" ID="-1" Wait="TRUE"/>
  <READ REGISTRY VALUE Key="HKEY_CURRENT_USER\\Software\\Professional Grade Macros\\Parameters\\ReturnString1" Destination="%Day%"/>
  <TEXT BOX DISPLAY Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang3081{\\fonttbl{\\f0\\fnil\\fcharset0 Tahoma;}{\\f1\\fnil Tahoma;}}\r\n\\viewkind4\\uc1\\pard\\f0\\fs16 Date1:%day% %date1%\r\n\\par Date2:%day% %date2%\\f1 \r\n\\par }\r\n" Left="Center" Top="Center" Width="278" Height="200" Monitor="0" OnTop="FALSE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>

which looks like this in English!

Date/Time: Set %Date1% to the current date/time using "dd mmm yyyy" as the format
Date/Time: Set %Date2% to the current date/time using "dd mmmm yyyy" as the format
Macro Run: { DateTime - Date to Character DOW }
Read Registry Value "HKEY_CURRENT_USER\Software\Professional Grade Macros\Parameters\ReturnString1" into %Day%
Text Box Display:

[terrypin]

Terry, I thought you had purchased the PGM Functions Library? If so, there are date functions contained within which should meet your needs.

 

As discussed in the thread

http://pgmacros.invisionzone.com/index.php?showtopic=3667

I thought I did have the library, from the CD in 'Macro Express Explained'. But, although Joe didn't explicitly say so in his subsequent reply, I assume that was a trial version as I get asked for a password. But as I understand it the library is being updated to ME Pro anyway, so I'll consider a licence when that's finished.

 

But which of the many PGM 'Date and Time' macros converts a date to a serial date?

 

BTW, as a footnote to the long-standing gripe by me and others about the inexplicable inability of this forum's search facility to allow 3-letter words, as for example discussed in this thread

http://pgmacros.invisionzone.com/index.php?showtopic=3711

 

add 'pgm' to the list!

 

--

Terry, East Grinstead, UK

[terrypin]

Here's a 3-line macro (ignoring the last line, and you need either line 1 OR line 2) that I think does what you want (I'm using today's date as my example):

<DATE/TIME Format="dd mmm yyyy" Flags="\xB0" Date="5/04/2009 10:22:45" Day_Offset="0" Month_Offset="0" Year_Offset="0" Hour_Offset="0" Minute_Offset="0" Second_Offset="0" Left="Center" Top="Center" Monitor="0" Variable="%Date1%" IsDateVar="FALSE"/>
  <DATE/TIME Format="dd mmmm yyyy" Flags="\xB0" Date="5/04/2009 10:22:45" Day_Offset="0" Month_Offset="0" Year_Offset="0" Hour_Offset="0" Minute_Offset="0" Second_Offset="0" Left="Center" Top="Center" Monitor="0" Variable="%Date2%" IsDateVar="FALSE"/>
  <MACRO RUN Use_ID="FALSE" Name="{ DateTime - Date to Character DOW }" ID="-1" Wait="TRUE"/>
  <READ REGISTRY VALUE Key="HKEY_CURRENT_USER\\Software\\Professional Grade Macros\\Parameters\\ReturnString1" Destination="%Day%"/>
  <TEXT BOX DISPLAY Content="{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang3081{\\fonttbl{\\f0\\fnil\\fcharset0 Tahoma;}{\\f1\\fnil Tahoma;}}\r\n\\viewkind4\\uc1\\pard\\f0\\fs16 Date1:%day% %date1%\r\n\\par Date2:%day% %date2%\\f1 \r\n\\par }\r\n" Left="Center" Top="Center" Width="278" Height="200" Monitor="0" OnTop="FALSE" Keep_Focus="TRUE" Mode="\x00" Delay="0"/>

which looks like this in English!

Date/Time: Set %Date1% to the current date/time using "dd mmm yyyy" as the format
Date/Time: Set %Date2% to the current date/time using "dd mmmm yyyy" as the format
Macro Run: { DateTime - Date to Character DOW }
Read Registry Value "HKEY_CURRENT_USER\Software\Professional Grade Macros\Parameters\ReturnString1" into %Day%
Text Box Display:

 

Thanks, but as per my other post I don't have a PGM licence.

 

Also, I'm not sure I see what it's doing. It appears to be converting the current date/time, whereas I want to be able to convert any date/time.

 

--

Terry, East Grinstead, UK

[paul]

Note the comment I included in brackets (I'm using today's date as my example). I could have provided any date, and in a multiplicity of formats!

[terrypin]

Note the comment I included in brackets (I'm using today's date as my example). I could have provided any date, and in a multiplicity of formats!

 

OK, thanks, missed that.

 

--

Terry, East Grinstead, UK