Need start date from a week #

[Himitsu]

I have made a calc. that pulls out which week we are in today. But what I need now is another field that shows the start date of that week number for the year that the record was made. What would that look like??

[comment]

If your week starts on Sunday:

date - DayOfWeek ( date ) + 1

or:

date - Mod ( date ; 7 )

If your week starts on Monday, use:

date - Mod ( date - 1 ; 7 )

[Himitsu]

that sounds good, but what it is is like a time clock solution. So, when the client clicks on login, there is the field that puts in the week #, say, this week is week 31. Now I have a relationship that connects to a global that has a list of the week #. By selecting the week, it will show all the dates that that employee has logged in. Does that make sense? So, it uses a kind of line item that has an autoenter field based on a calc. to get the week 3. I want it to be a calculation not an autoenter field. Because if, for some reason, the date of log in was wrong, and it had to be changed, and that put the week number up one or down one, then the field would still show the old week #. So I would like to somehow get the field that has the week #, and produce the start date for that week. From that, I can easily find the end.

I once saw something like that on one of Matt's vids... but I was looking through them and couldn't find it.

I know it isn't too easy because depending on the year, the week start date will be different. so it has to include the getYear of the record created date. Then base that from there.

I played with the getDate () function doing:

getDate( year (datefield); month (weekofyear (weekfild))

but that had no luck.

Any ideas? of maybe it is easy... I just can't see it...

[comment]

I am afraid I couldn't quite follow your explanations.

As you say, the week number alone, without knowing the year, is not enough to compute the date. And you haven't even told us how you compute the week.

IMHO, week number is good for display only. If you want to group records by week, use the starting day of the week (a calculated DATE field) as the link. I think that is the easiest way, since the year is already there, and it will also sort correctly. IOW, the order of computing should be date -> week_start_date -> week_number (instead of date -> week_number -> week_start_date).

[Himitsu]

well, making the week number is easy. There is a date field, so the calculation is just WeekOfYear ( Get ( CurrentDate ) )

I do have a field of the date that the record is made. How would I determin the start date of that week, ie starting with sunday to saturday using that date then?

[comment]

See my first reply in this thread?

[Himitsu]

I have been trying that calc and different ways of doing that... but it either shows the wrong date, or just a "?". I have an autoenter field that enters the creation date, called EMP_date.

So I used that first. Then a combination of get(currentDate) and so on. So the field has 7/23/2005 in it. So the week start date should be either 7/17/2005 if a sunday or 7/18/2005. Am I wrong for assuming that?

[comment]

I am not quite sure what Get(CurrentDate) has to do with this.

Your weekStart field should be a calculation field (result is Date ) =

EMP_date - DayOfWeek ( EMP_date ) + 1

This will return 7/17/2005 (Sunday) when EMP_date is between 7/17/2005 (Sunday) and 7/23/20005 (Saturday), inclusive.

If you want a portal of records created in the current week, create another calculation field (unstored, result is Date ) =

Get(CurrentDate) - DayOfWeek ( Get(CurrentDate) ) + 1

and base the relationship on a match between the two calculation fields.

[Himitsu]

comment... I am really really sorry... I wasted your time... I was cracking at it and then I decided to break it down but making each field to get teh day of the week then minus that from the date... after doing that, I realized that my stupid date field I was using was infact set to a text field and not a date field... comment, I am really sorry... I don't know how this slipped through my site... I will be more careful about these things in the future. Thanks for your time.

[comment]

Don't worry about it - it can happen to anybody (certainly to me!)