Jump to content
Salesforce and other SMB Solutions are coming soon. ×

mm/dd/yyyy


Doctor Ahhrrr

Recommended Posts

hello! i'm sure this is an obvious one, BUT after an hour or so of frustration, i have to ask!

 

SO... how do you control your date fields so that they return leading zeros when necessary? for example, I want June 8, 2003 to come up with 06/08/2003 instead of just 6/8/2003. i don't see anything helpful in the options window.

 

thanks in advance!

Link to comment
Share on other sites

Under the format menu, "Date" provides a multitude of methods of formatting your date as well as the ability to format in tje European fashion (DD/MM/YY).

 

Tom

Link to comment
Share on other sites

that did the trick for the formatting problem. thanks for your help.

 

but the reason I was interested in the leading zeros is because i am using the date to calculate the serial numbers for the records. for example, june 9, 2003 should create the serial number of 20030609, not 200369. I need this so that the serial numbers will sort correctly; the serial number for the December 25 2002 (20021225) record must be lower than the serial number for the June 9, 2003 record.

 

so i set this up:

Date = Date field (now with the leading zeros)

 

DayID= calculation field = Year(Date) & Month(Date) & Day (Date)

 

but I'm still getting 200369 in DayID for in the above case. any advice out there?

 

thanks again for all the generous support!

matthew aka Doc R.

Link to comment
Share on other sites

Originally posted by chopper:

[qb]Done it layout mode , click on field then menu bar and Date (format)[/qb]

Do the same with number format
Link to comment
Share on other sites

hi friends,

 

well i couldn't find anything in the number format options to help me (Leave data formatted as entered, Format as Boolean, Format as decimal).

 

but "masking" my month and day did the trick! is there anywhere i could read up on the concept of using masks in calculations? thats new to me...

Link to comment
Share on other sites

Hi,

I believe that the formula you're looking for is:

 

Year(Date) & Right("00" & Month(Date), 2) & Right("00" & Day(Date), 2)

 

...which will add the leading zeros to your month and day values by processing them as text. wink.gif

Link to comment
Share on other sites

hiya cobalt, well doug's simpler formula worked for me so far. should i use yours instead? will it stave some potential problems as the database gets populated?

 

i'm also interested in understand WHY it works. if i were to predict the result of a formula like that - with the zeros contained in qoutes the way it is - i'd guess it would add 2 zeros PLUS the number; July would bring "006" and December would "0012". so if anyone cares to break it down for educational purposes, or suggest something i could read, that would be great!

 

thanks for everyone's input.

Link to comment
Share on other sites

Colbalt's formula is conditioned by the "Right" function. In his example he is always taking the 2 rightmost digits, thus will give 06 instead of 006.

 

I do not know of limitations in my original example when used as you described in a calculation field, however, Colbalt's formula will work as well.

Link to comment
Share on other sites

thanks for that explanation of why cobalt's "forum-ula" (sorry, couldn't resist) works. but yours works also, bringing back the same number, without the "Right" function. isn't that interesting? it seems to me just "00" & Month(Date) - should bring back "006" for June, but it brings "06" ....

Link to comment
Share on other sites

My formula works primarily due to the way FileMaker handles the numbers in the formula.

 

It is important to remember that & and + are two different things.

 

The plus sign (+) is 'addition', whereas the ampersand (&) is 'append'.

 

In a calculation, 1 + 1 will give a result of 2;

1 & 1 will give 11.

 

My use of "00" + Day(date) relies on the fact that Filemaker does not drop the leading zero in this instance. Thus: 00 + 6 gives 06 (instead of just 6 as might be expected).

 

Colbalt's formula removes all doubt by putting the zeros before the date and then taking the "last" [rightmost] two digits.

Thus "00" & 6 gives 006 which is then shortened to 06 with the use of the Right("data", 2) function.

 

Anyway, I am glad you have got a working solution (or two).

Link to comment
Share on other sites



×
×
  • Create New...

Important Information

Terms of Use