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

Current Week Query


Recommended Posts

I'm looking to do something very simple: I would like to identify the current week in a report I'm generating of college student visits. In other words, I would like to show a text line that reads:


Recruiting Visits for the week of: March 5-9, 2008


Any help?



Link to comment
Share on other sites

Your weeks run Wednesday through Sunday? huh?


Any date falls within a given week, that week can be expressed as a number (sometimes fiscal etc.,) or as a date range running usually Mon-Fri, Sun-Sat or whatever... in your case it runs Wed-Sun (it don't matter if this is wrong, same rules apply)


Given any date, FMP will provide a calculated day name for that date, build a date/text calculation based on date of visit (any date during the visit would work unless the visit spans ? 2 weeks


If the date of my visit is 08/06/08 FMP knows this is a Wednesday, and if my weeks run (as example) mon-fri and the dayName does not = monday, it ain't too hard a calculation to say... this visit is for week Monday 4th - 9th June 2008


What you gotta watch for (the fun bit!) is where the week runs through two months or two years, play with the date function calcs and you should get it, and if the report is required ONLY for current week... hey! (as Fonzie used to say)

Link to comment
Share on other sites



My example was indeed random. Our week runs M-F.


I appreciate your comments. Can you provide any examples of calculations for me to try? I'm afraid I'm not having any luck on my own.

Link to comment
Share on other sites

I would build a calc like this... a bunch of, if this and that stuff, or you could use case statements. You could also compress everything into one calculation (and I will leave that for you to do), but then it's easier to trace, test and audit when things are in small packets, (note your email address if you want it sent as a file) so...


You need a date field and you have one in your visit date, it doesn't matter if the date is the start of visit date, or end of visit date... just so long as it is a date which falls within the period of the visit itself, you will need this field to start the calculation... can we just call this field [date] (change in calc to your own field name)


Find the records you want for the report, the calculation will take the [date] from the first record of the found set. Create and define four new (calculation) fields as follows:


1. dayCalc=DayName(date)


2. weekstartingDate=If(dayCalc="monday", date, If(dayCalc = "tuesday", date - 1, If(dayCalc="wednesday", date-2, If(dayCalc = "thursday", date - 3, If(dayCalc = "friday", date - 4, If(dayCalc = "saturday", date - 5, date - 6))))))


3. weekendingDate=weekstartingDate+5


4. Result="Recruiting visits for week: " & If(Month(weekstartingDate) = Month(weekendingDate), Day(weekstartingDate) & " - " &Day(weekendingDate) & " " & MonthName(weekstartingDate) & " " & Year(weekstartingDate), Day(weekstartingDate) & " " & MonthName(weekstartingDate) & " - " & Day(weekendingDate) & " " & MonthName(weekendingDate) & " " & Year(weekendingDate))

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

Terms of Use