Calculating Per Diems Across Multiple Weeks
I am really stumped on this one and need some help. The user will select the Hotel In - is on or after (this is actually the Hotel Check-Out Date to give me one part of the range). Then they will sel...
Forum Discussion
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!