Day of the week calculation

[zenmom]

Hi collective wisdom! After a few months, I am looking at FileMaker again. I'm trying to create a db where I can enter a date in one field, and then display in another field the day of the week ("Tuesday" etc). Then I could sort to find a subgroup of all events that happen on a Tuesday. I've looked in the FM 8.5 help files, but I seem to be missing a step somewhere, because I just get "?" in the calculated field. I've got my DayofWeek1 field set up as a calculated field. I've got a field named Date1 into which I enter my desired date. Then the field DayofWeek1, type Calculation, =DayName ( Date1 ). What am I missing? It seems like it ought to be easy, but I've looked at it 'til I'm cross-eyed and can't see it. Thanks in advance for any advice.

[aholtzapfel]

Check what data type the calculation returns (should be text, is Number by default)

[zenmom]

Yup, it's set to "text".

[Genx]

What data type is the field you're entering the date into?

[zenmom]

Type is set to Calculation in Define Database window.

[Genx]

No no, the field your entering the data into i.e. Date1

[aholtzapfel]

I think genx means the are you entering your date into a date field?

[Totes]

I think this may be what you are looking for...or at least I have this calc on one of my layouts and it gives me "Saturday" if I enter 7/14/2007 in my date field.

DayName ( your date field goes here )

Make sure the calc is set to "Text" and "Unstored"

Man this could be my first offical help.

James

Edited July 15, 2007 by Guest

[zenmom]

Thanks all for your suggestions! We have success.

I checked the data type for the Date1 field, and sure enough had forgotten to change it from Text to Date. So I took care of that. But... still got "?" in my calculated field. Then out of curiosity I changed what I'd entered in the Date1 field from "July 1, 2007" to "7/1/2007" and VOILA! The calculated field came to life!

Now this just leaves me wondering whether or not FM should be able to recognize dates in the first format or not. I'd think it would be smart enough to figure out that's a date?

Thanks again for your help.

[Genx]

You can format a date to appear however you wish (enter layout mode, select your field and go to format -> date).

An item of text however, means very little to FileMaker.

You could really enter that date in any format though couldn't you... I mean: July 1, 2007

1 July 2007

July 1 2007

July, 1, 2007

July/1/2007

July-1-2007

1st of July 2007

July the 1st 2007

The first of July 2007

One July 2007

July One 2007

You could go on forever with that list. If you really wanted to use that sort of format however, and you were to strictly stick by that format, a function to convert it to a standard date string isn't difficult to write.

[comment]

Filemaker is able to recognize dates entered in the short-date format of your computer's operating system (or the computer that created the file - see File > File Options).

If you intend to use this to link records of the same day-of-week, it would be better to use the DayOfWeek() function, with a Number result. And keep it stored, unless you have a good reason not to.

[zenmom]

Okay, Genx, lemme see if I've got this: FileMaker can easily format the *display* of a date any way I'd like to see it, but for input it's best to stick to a more standard MM/DD/YYYY format? I can do that.

comment, thanks for the suggestion to keep the value stored. (I've got another field using DayOfWeek() function which I use for sorting.)

I've got another question now, but I think I'll post it as a new topic.

Thanks again for all the help.

[Genx]

FileMaker can easily format the *display* of a date any way I'd like to see it, but for input it's best to stick to a more standard MM/DD/YYYY format

Yep :