Date formula to exclude weekend 1

[buckslor]

Hi

Is it possible to create a date formula to run the previous work day's date that can exclude weekends?

For example, if today is Monday and you run the report, it'll show Friday's data. Tuesday show Monday, Wednesday show Tuesday, ect. without having to manually click and select a date.

The reason for this is I have a software program the generates crystal reports automatically. I'm hoping that there's a formula that can be inserted into Report>Select Expert for the date field for this to work, but I have no idea.

Thank you in advance.

 

[Madawc]

If you need the day of the week, that's

DatePart ("w", {date1})

Put this as a formula field and it gives a number, 1 to 7. The default is Sunday as 1 and Saturday as 7, though you can change this using firstDayOfWeek

To get the last working day, you could do something like

If DatePart ("w", {your.date}) = 1 then currentdate-3
else currentdate-1

That's assuming it is only run on weekdays, and ignores bank holidays (which mostly need their own table).


Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[buckslor]

Hi Madawc

That's exactly what I was looking for. I never even thought of using DatePart.

Many Thanks