Formatted Date to Number

[zach20]

Good Morning,

 

I have a Date field that it set to get the current date in the format "060108" for June 1, 2008, as an example. The formatting itself is set to display 6/1/08 in the Date Format tab, but I removed the "/" separator and have set the Leading Day and Leading Month attributes to "0".

 

I was wondering if it's possible to convert this field to a number while keeping everything intact, or if there's a way to format the field as a number without having to classify it as a date in the first place. Everything I have tried has resulted in the Date losing its leading "0" characters.

 

I would keep the field as a Date, but the problem lies in using a search. I cannot search "060108" to find the corresponding record, I have to search "6/1/08". That would also be fine if we didn't use the "060108" format as a Purchase Order # instead of as a true date...but that kind of change isn't up to me smiley-frown

 

Thanks!

Zach

[Techphan]

I am sure that there will be some error that needs to be corrected in what I write, but here goes anyway:

 

 

YourField -> 061208 or MMDDYY (6 digits that you want to treat as a date)

 

Make a calculated date field: DateCalc= Date (Left(YourField;2); Middle (YourField;3;2) ; "20" & Right (YourField;2))

 

The DateCalc (if equation is correct) will give you 6/12/2008.

 

You can set up a calculated SearchDate field the exact same way.

 

SearchDate = number (or text will work) entry, your "060108" (no quotes " " though)

 

cSearchDate = same equation as above, then use this result in a script to search DateCalc.

 

 

 

Edit add: By the way, I do something similar to what you outlined above except I use three fields (so I do not have to use leading zeroes for day and month, but there are Return or Enter or Tab key hits instead. If FM gets around to adding the feature of automatically jumping to the next field after entry of a second digit/number entry, then Return/Enter/Tab would be eliminated):

 

MonthEntry

DayEntry

YearEntry

 

I use these fields to create a date if necessary for search: Date(MonthEntry;DayEntry;YearEntry)

[zach20]

I think I confused you a little bit and I do apologize for not explaining it more clearly.

My field is set to

get ( currentdate )

and that is what displays the "061208". What I'm trying to do is get FM to treat "061208" as a number so that I can work with the field values from there. Until I do this, FM is treating my "061208" as "6/12/08" which I don't want it to do. Essentially I want to convert an auto-enter date into a plain number. My problem is that when I do this, I lose the leading "0" characters, so "061208" turns into "61208" and I can't seem to stop this correction.

 

Is that explained a little better?

 

What I may do is what you suggested by meaning of the three fields. I can have FM get the current month, if the value of current month is only 1 character, slap a "0" in front of that and apply the same to the 2 other fields (day and year). From there I think I'll have one field that contains all three values, essentially showing 1 total value to the user.

 

currentmonth = 06, currentday = 12, currentyear = 08

bigfield = currentmonth & currentday & currentyear

 

I'll then use this total value in place of what I'm trying to do regarding the question of this thread. Do you think that would be a lot easier? I know I could do it this way without any problems.

[Molson]

What I'm trying to do is get FM to treat "061208" as a number so that I can work with the field values from there.

 

Hi Zach,

 

Would the fact that a date is really a number behind the scenes be of use to you?

 

Your example 061208, the displayed date, has a numerical value of 733205. The day before [6/11/08] has a numerical value of 733204; the day before [6/10/08] has a numerical value of 733203, etc.

 

You can easily see the number value by creating a calc field set to the date field with a numerical result.

 

HTH,

 

Michele

[zach20]

Hi Michele,

 

I actually really like the way that turns out. I think that method of numbering is a lot better than the method we currently use, and a lot easier to work with in FM considering what I'm trying to do with the number. I also like that it works the other way around, the number can turn into the date in which it came from.

 

I'm definitely going to look for a way to use this method.

 

Thank you both very much smiley-smile