Beast of Date/Time issue - Need days and/ or hours

[agray123]

OK here we go -

Extracting data to build an expense report: I have a date/time field for Begin date and one for end date.

Need to be able to caclulate per diem for full 24 hour days AND be able to calculate per diem for each hour beyond a full day (X the breakdown hourly rate for per diem)

Example - employee leaves 4/5/04 @ 2:00 pm

Returns 4/09/04 @ 7 pm

Has spent 4 24 hour periods (per diem *4)and one 5 hour period - therefore according to our wonderfully worded (and always subject to change) travel requirements:

Overnight travel for a partial day following a full 24 hour period is eligible for reimbursement based on the table below.

0 to less than 3 hours 0.00 (normal) 0%high cost
3 to less than 9 hours 7.00 (normal) 25% (high cost)
9 to less than 15 hours 14.00 (normal) 50% (high cost)
15 to less than 21 hours 21.00 (normal) 75% (high cost)
21 to 24 hours 28.00 (normal) 100% (high Cost)

High cost will be identified as any $ amt greater than 28$ (non CONUS areas)

 

[mbarron]

If I interpetted your post correctly, for the time period you have used the total is $119. (28 * 4 + 0.25* 28 ) if that's the case, this should do the trick:

whileprintingrecords;
numbervar tothours:=datediff("h",{@end},{@start});
numbervar days:=truncate(tothours/24) ;
numbervar hours:=remainder(tothours,24);
numbervar partial;

if hours < 3 then partial:=0 else
if hours < 9 then partial:=.25 else
if hours < 15 then partial:=.5 else
if hours < 21 then partial:=.75 else
partial:=1;

numbervar out:= days *28 + partial *28;

Mike

 

[agray123]

Awesome - It worked to perfection - but the report asks for

Estimated Subsistence for X days @ (cost) per day
Plus (hours) @ (amount) per 6 hour period.

I guess I need to put the breakdowns of the variables in each area.

 

[agray123]

IS there a way for me to put each individual arrive date in this formula -so I can geterate a detail of how many days spent at each hotel and at which subsistence rate - I am looking for results like this

1/2/5 days at 109/34/99 dollars = sum

1/2/5/ days at 44/28/43 dollars = sum
plus 7 hours at 7.00 an hour (per 6 hour period)