7 Replies Latest reply: May 15, 2014 9:52 AM by Tom Strachan

# Time calc

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

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

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/cgi-bin/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 14-Jul-2006, if you define a calculated date field with the following formula:

[font="courier"]CtoD(&quot;01-01-1900&quot;,&quot;m/d/y&quot;)+-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

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/cgi-bin/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 14-Jul-2006, if you define a calculated date field with the following formula:

[font="courier"]CtoD(&quot;01-01-1900&quot;,&quot;m/d/y&quot;)+-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

Hi Tom,

If you know the base date (in this case 01-01-1900) you can set up a calculated field directly to store the constant (maybe as a run time parameter?) and express the formula as +(days-2).

I think the issue about the -2 relates to the 01-01-1900 date being day 1 (So day zero is 31-12-1899 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

+(DAYS-1)

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;

{31-12-1899}+(DAYS-1)

[size="1"][ July 17, 2006, 04:19 AM: Message edited by: Grant Perkins ][/size]

• ###### Time calc

Hi Tom,

If you know the base date (in this case 01-01-1900) you can set up a calculated field directly to store the constant (maybe as a run time parameter?) and express the formula as +(days-2).

I think the issue about the -2 relates to the 01-01-1900 date being day 1 (So day zero is 31-12-1899 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

+(DAYS-1)

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;

{31-12-1899}+(DAYS-1)

[size="1"][ July 17, 2006, 04:19 AM: Message edited by: Grant Perkins ][/size]

• ###### Time calc

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

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).