Date Shift Problem

[#1]

Dear All,

I have a file which shows holidays for employees. The data field format is as below:

1001M01

• First two character presents years
• Second two character presents months
• Remaining part of the field is not important

When the employee has an holiday at December it shows in next month as January in the system. I would like to show it in exact date as December. How can I make this shift.

[erichshiino]

Hi,

First, we need to transform your string into a date.

=makedate(left(string,2)+2000, num(mid(string,3,2)))

Second, we shift this date to the previos month.

= addmonsths( makedate(left(string,2)+2000, num(mid(string,3,2))) , -1)

Rgds,

[#3]

Hi Erich,

Thanks for you solution. It works for years which are greater than 2000. There are also some information for 1999, 1998, 1997, etc. In above solution those years look like as 2099, 2098, 2097. What do you suggest for this?

Kind Regards,

[chris_johnson]

Hi,

I suppose you would need to set a point at which the birthdays are either in the 1900's or the 2000's. For example, if you say that anyone with a birthday after the year 20 is going to be in the 1900's then:

= if (left(string,2) <= 20, addmonsths( makedate(left(string,2)+2000, num(mid(string,3,2))) , -1),

addmonsths( makedate(left(string,2)+1900, num(mid(string,3,2))) , -1))

I'm sure there's probably a way to make a check on the current year to see if the birth year further forward than the current year, maybe:

= if(left(string,2) <= year(today(0), addmonsths( makedate(left(string,2)+2000, num(mid(string,3,2))) , -1),

addmonsths( makedate(left(string,2)+1900, num(mid(string,3,2))) , -1))

Chris

[Miguel_Angel_Baeyens · Accepted solution]

Hi, I agree with Chris, there must be in some point where you have to say if it's 20th or 21st century.

But for the time being, taking a loot at your data, the following should be enough to transform field CJ into Date

=Date(Date#(Left(CJ, 4), 'YYMM'))

Those that start in 99 will be considered as 1999, those that start in 00 or greater will be considered as 2000.

Hope that helps.