
Time calc
Tom Strachan May 15, 2014 9:52 AM (in response to Tom Strachan)Hello everyone. Dumb question. I have a file that expresses a date as an integer, the number of days since 1/1/1900. For example 38123.
I have to convert this number to a more normal date format such as xx/xx/xx.
Easy in Excel, but I'm stumped. what can I do in Monarch to handle this conversion?
Thanks!

Time calc
Data Kruncher May 15, 2014 9:52 AM (in response to Tom Strachan)Hi Tom,
I don't think this is a dumb question at all; quite to the contrary.
So, thinking that you can [url="http://mails.datawatch.com/cgibin/ultimatebb.cgi?ubb=get_topic;f=1;t=001244#000000"]subtract one date from another[/url] to get the number of days between those dates, I thought you should be able to add a number of days to a date to determine another date.
Given that Excel returns 38912 for 14Jul2006, if you define a calculated date field with the following formula:
[font="courier"]CtoD("01011900","m/d/y")+2 /font[/quote]Monarch will return a date of July 14, 2006 when the Number of Days numeric field equals 38912. Not sure of the specifics as to why you need the 2, but you do.
That work for you?
Kruncher
Edit: In fact, there could be any number of applications for adding a number to a date found within a report: calculating payment due dates (maybe with an external lookup to a customer file to determine the customer's payment terms in days + the invoice date, if not everyone has, say, 30 day terms), expiration/shelf dates of food items... Does anyone currently use a calculation like this?
[size="1"][ July 14, 2006, 10:40 PM: Message edited by: Data Kruncher ][/size]

Time calc
Data Kruncher May 15, 2014 9:52 AM (in response to Tom Strachan)Hi Tom,
I don't think this is a dumb question at all; quite to the contrary.
So, thinking that you can [url="http://mails.datawatch.com/cgibin/ultimatebb.cgi?ubb=get_topic;f=1;t=001244#000000"]subtract one date from another[/url] to get the number of days between those dates, I thought you should be able to add a number of days to a date to determine another date.
Given that Excel returns 38912 for 14Jul2006, if you define a calculated date field with the following formula:
[font="courier"]CtoD("01011900","m/d/y")+2 /font[/quote]Monarch will return a date of July 14, 2006 when the Number of Days numeric field equals 38912. Not sure of the specifics as to why you need the 2, but you do.
That work for you?
Kruncher
Edit: In fact, there could be any number of applications for adding a number to a date found within a report: calculating payment due dates (maybe with an external lookup to a customer file to determine the customer's payment terms in days + the invoice date, if not everyone has, say, 30 day terms), expiration/shelf dates of food items... Does anyone currently use a calculation like this?
[size="1"][ July 14, 2006, 10:40 PM: Message edited by: Data Kruncher ][/size]

Time calc
Grant Perkins May 15, 2014 9:52 AM (in response to Tom Strachan)Hi Tom,
If you know the base date (in this case 01011900) you can set up a calculated field directly to store the constant (maybe as a run time parameter?) and express the formula as +(days2).
I think the issue about the 2 relates to the 01011900 date being day 1 (So day zero is 31121899 which you could use instead as the runtime parameter.
I suspect the final adjustment (the minus 1) is a similar issue in that the day number, when converting to a date by calculation, is assumed to be a completed number of days thus the date offered is the next day date not the 'current day' date. In other words the calculation is geared up for the results of subtraction rather than addition.
Personally I think I would settle for a calculated date format field with
as a fairly memorable way write the formula.
HTH.
Grant
Edit:  In the above formula the date represents the field that would be used for the PARAMETER FIELD.
To make the formula using a CONSTANT DATE would require;
{31121899}+(DAYS1)
[size="1"][ July 17, 2006, 04:19 AM: Message edited by: Grant Perkins ][/size]

Time calc
Grant Perkins May 15, 2014 9:52 AM (in response to Tom Strachan)Hi Tom,
If you know the base date (in this case 01011900) you can set up a calculated field directly to store the constant (maybe as a run time parameter?) and express the formula as +(days2).
I think the issue about the 2 relates to the 01011900 date being day 1 (So day zero is 31121899 which you could use instead as the runtime parameter.
I suspect the final adjustment (the minus 1) is a similar issue in that the day number, when converting to a date by calculation, is assumed to be a completed number of days thus the date offered is the next day date not the 'current day' date. In other words the calculation is geared up for the results of subtraction rather than addition.
Personally I think I would settle for a calculated date format field with
as a fairly memorable way write the formula.
HTH.
Grant
Edit:  In the above formula the date represents the field that would be used for the PARAMETER FIELD.
To make the formula using a CONSTANT DATE would require;
{31121899}+(DAYS1)
[size="1"][ July 17, 2006, 04:19 AM: Message edited by: Grant Perkins ][/size]

Time calc
Tom Strachan May 15, 2014 9:52 AM (in response to Tom Strachan)Thanks very much both of you, I'll use these ideas immediately. It does simplify the formula considerably, and reduces further coversion work (i.e. eliminates the need for Excel to do the work).

Time calc
Tom Strachan May 15, 2014 9:52 AM (in response to Tom Strachan)Thanks very much both of you, I'll use these ideas immediately. It does simplify the formula considerably, and reduces further coversion work (i.e. eliminates the need for Excel to do the work).