Payroll End Date - Report Designer

Question

Is there a formula to determine the day of the week in Report Designer? (as in Sunday) I'm working on a LCR report to send weekly - my problem is the report isn't always run/sent on the same day each week. I need to determine the system date and subtract # of days to get back to our PE date - Sunday. I can get the report to work if we run the report on the same day each week, but ideally I would like the report to be flexible on the date run. Any suggestions?: 
 FYI - using My Assistant to run the reports and send. There may be a way in My Assistant that I don't know about. 
 Thanks in advance :)

Art Minds · Accepted Answer

Teresa J, 
 Here's a suggestion that doesn't require determining the day of the week, but instead determines the current (most-recent) period end date based on the elapsed number of days from a known Sunday date (for example, the first Sunday of the year). 
 Create these formulas: 
 Cumulative days: 
 DAYS(System date, NEWDATE(0, 1, 4, 2015)) 
 [This formula calculates the elapsed number of days between the first known Sunday of the year and the System Date.] 
 Week number: 
 ROUND(Cumulative days (Design Formula) / 7,0) 
 [This formula calculates the number of whole weeks that have elapsed since the first Sunday of the year, by calculating the number of days between the first Sunday of the year and the System Date, and then rounding to a whole number.] 
 Current P/E Date: 
 NEWDATE(First P/E date of year (Design Formula), 0, Week number (Design Formula) *7, 0) 
 [This formula calculates the most recent Sunday date, which is your current period end date, by multiplying the number of full weeks that have elapse by 7 (# of days in a week) and using the result in a NEWDATE formula that begins with the base date of the first Sunday of the year] 
 You can then use the results of the formula "Current P/E Date" in any other formula to filter your data or to include in heading text. This formula would not need to be changed annually and there are always 7 days in a week, even leap years. 
 Hope this helps. 
 Art Minds 
 Consultant

Art Minds · Answer

Teresa J, 
 I apologize for the oversight, but here is a correction to my formula for calculating the week number. Substitute the ROUND function with the TRUNC function to prevent rounding up of the week number if the date you run the report is 4 or more days beyond the period end date. 
 Here is the corrected formula: 
 Week number: 
 TRUNC(Cumulative days (Design Formula) / 7,0) 
 Art Minds

Sage Construction & Real Estate

Payroll End Date - Report Designer

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING