Forum Discussion
HansHamm
8 years agoQrew Assistant Captain
Hey Mark... this didn't work for me, but thinking about it I came up with the following:
Added a field in the table to provide the date of the second Monday in the record
If([Number of Nights Out]>7,
If(Today()>[Hotel Check-In Date],LastDayOfWeek(Today())+Days(2),
If([Hotel Check-In Date]>=Today(),[Hotel Check-In Date])))
Then added this formula in the report to reference the aforementioned field:
If((If([Number of Nights Out]<=7,[Number of Nights Out],
If([Per Diem Date]=[Hotel Check-In Date],7,ToDays([Hotel Check-Out Date]-[Per Diem Date])))*25)>=175,175,
If([Number of Nights Out]<=7,[Number of Nights Out],
If([Per Diem Date]=[Hotel Check-In Date],7,ToDays([Hotel Check-Out Date]-[Per Diem Date])))*25)
So far it seems to work... I appreciate your help as it did start me thinking in a different direction!
Added a field in the table to provide the date of the second Monday in the record
If([Number of Nights Out]>7,
If(Today()>[Hotel Check-In Date],LastDayOfWeek(Today())+Days(2),
If([Hotel Check-In Date]>=Today(),[Hotel Check-In Date])))
Then added this formula in the report to reference the aforementioned field:
If((If([Number of Nights Out]<=7,[Number of Nights Out],
If([Per Diem Date]=[Hotel Check-In Date],7,ToDays([Hotel Check-Out Date]-[Per Diem Date])))*25)>=175,175,
If([Number of Nights Out]<=7,[Number of Nights Out],
If([Per Diem Date]=[Hotel Check-In Date],7,ToDays([Hotel Check-Out Date]-[Per Diem Date])))*25)
So far it seems to work... I appreciate your help as it did start me thinking in a different direction!