Text Date to Date

[LaRetta]

Our product expiration dates are only month and year but I need REAL dates. Users type into text field (ExpDateTxt) as 03/8 or 4/10 or 11-10 etc. First is the month, second the year. This needs to be converted to real date of that month/year and the last day of that date.

I've come up with this but it feels convoluted. I want to take into account slashes and dashes and whether they enter a leading zero. My thought was to strip slash/dash, replace with space then use simple LeftWords() RightWords(). Is there a cleaner way of writing it? And can anyone see any other possible 'gotchas' that I'm not accounting for? It is very important that the resultant Auto-Enter (Replace) ExpireDate be right, as it's used in stock pulling.

Let ( [

base = Substitute ( ExpDateTxt ; [ "-" ; " " ] ; [ "/" ; " " ] ) ;

m = LeftWords ( base ; 1 ) ;

y = "20" & Right ( "00" & RightWords ( base ; 1 ) ; 2 )

] ;

Date ( m + 1 ; 0 ; y )

)

And don't ask me what happens when we hit year 2100! I considered using the left two of current year instead but it will still break if the future is NOT the current first two of year. Ideas appreciated.

EDIT: Our expiry years are safely within 50 years. Maybe I could use that as breakpoint. Of course I won't be around to see it break, will I?

I want it right and safe, however so would prefer using the best formula possible.

LaRetta

Edited August 20, 2005 by Guest

[RalphL]

Try this it worked for me in a couple of tests.

Let ( [

base = Substitute ( ExpDateText ; [ "-" ; " " ] ; [ "/" ; " " ] ) ;

m = LeftWords ( base ; 1 ) ;

cy = Year ( CreationDate ) ;

y = (Left ( cy; 2 ) & Right ( "00" & RightWords ( base ; 1 ) ; 2 )) + 100 * (Right (cy ; 2) > RightWords ( base ; 1 ))

] ;

Date ( m + 1 ; 0 ; y )

)

I choose to use creation date as a reference year since it should not change.

Edited August 20, 2005 by Guest
Corrected Y

[comment]

I think your calc could be simplified to:

Let ( [

base = Substitute ( ExpDateTxt ; [ "-" ; " " ] ; [ "/" ; " " ] ) ;

m = LeftWords ( base ; 1 ) ;

y = RightWords ( base ; 1 ) + 2000

] ;

Date ( m + 1 ; 0 ; y )

)

and if you want to future proof it, perhaps something like:

Let ( [

base = Substitute ( ExpDateTxt ; [ "-" ; " " ] ; [ "/" ; " " ] ) ;

m = LeftWords ( base ; 1 ) ;

cy = Year ( Get ( CurrentDate ) ) ;

y = RightWords ( base ; 1 ) + Div ( cy ; 100 ) * 100 ;

yy = y + 100 * ( y < cy )

] ;

Date ( m + 1 ; 0 ; yy )

)

But the real thing that bothers me is why do you people have to enter this manually in the first place, instead of calculating it from date of manufacture and product shelf life. Sooner or later, there's going to be a human error on data entry.

[LaRetta]

Ralph, thank you! I was playing with boolean test to jump left 20 or +1 ahead. But I kept doubting the efficiency of the entire calculation.

But the real thing that bothers me is why do you people have to enter this manually in the first place, instead of calculating it from date of manufacture and product shelf life. Sooner or later, there's going to be a human error on data entry.

Agreed. In fact, your words to me on a prior post (on something similar) blared in my mind. But I was nix'd in Management decision. And that's why I need to consider many possibilities and I'm even considering testing for alpha characters or resultant dates less than current date.

Our stock comes in and is entered into inventory by shipping. What shows on all documentation (Packing Slip, etc) is M/Y and that's what shows on our products. So that's how they insist on entering our receiving. Purchasing does not specify Lot# or ExpireDates when they send POs to our manufacturers. The manufacturers determine this and we don't know what it is until stock arrives. They are entered into FM directly from manufacturer packing slips (which also list m/y). We have no other dates (date of manufacture or product shelf life). I tried using two number fields but they kept entering the whole thing in first number, etc.

I see their point also. But it scares me too. I need better protection for them. Eventually, I hope to implement scan bars but that's down the road. I'm open to other options, calculations or structure to accomplish it; as long as the User gets one field and isn't required to type a day. What I do behind the scenes is under my control. But I don't feel in control of this process at all and really appreciate the help!

LaRetta

[comment]

I'd think shelf life is a constant per product. Perhaps you could check if today + product::shelf life is approximately equal to expiration date.

[LaRetta]

Excellent. I will have them plug the shelf life in Products. Some are in; most aren't. We just recently (within a year) fell under rules about listing an expiration. That's why this has never been tracked before. I'm using your second calculation.

The beauty of your calc is that, even upon someone typing 'mt' and nothing else, it generates 12/31/2099! No expiration year (for products manufactured in 2005) would be greater than 2015! And if we ever manufactured a product with a 90-year shelf-life, I don't think I'd want to take it!!! HAH! So this eliminates the need to test for other wonky characters, etc!

I have added that validation to the ExpireDate result. Let them deal with THOSE then. And I've placed the ShelfLife field right next to it so (if they don't have it filled beforehand), they can fix it then. :bigshades: