I have two fields in a subreport that display punches for each employee for each day of the week. If, and only if, that employee takes a lunch, there will be 4 punch times listed. I need to subtract the first "out" punch from the second "in" punch.
{V_TA_PREPOST.START_TIME} and
{V_TA_PREPOST.END_TIME}
If someone takes a lunch their punches for the day would look like so:
in out
7:00 am 11:30 am
12:00 pm 3:00 pm
I created a formula called "lunchflag" that determines whether there are four punches:
If DistinctCount ({V_TA_PREPOST.START_TIME})= 2 and DistinctCount ({V_TA_PREPOST.END_TIME})= 2 then "1"
Else ""
Now what I (think I) need to do is make another formula and take the two middle punches and subtract them which will result in the amount of time they took for lunch:
"If @lunchflag = 1 then blahblahblah"
It's the "blahblahblah" part where I'm stuck. How can I tell the formula to only subtract the two "middle" punches?
{V_TA_PREPOST.START_TIME} and
{V_TA_PREPOST.END_TIME}
If someone takes a lunch their punches for the day would look like so:
in out
7:00 am 11:30 am
12:00 pm 3:00 pm
I created a formula called "lunchflag" that determines whether there are four punches:
If DistinctCount ({V_TA_PREPOST.START_TIME})= 2 and DistinctCount ({V_TA_PREPOST.END_TIME})= 2 then "1"
Else ""
Now what I (think I) need to do is make another formula and take the two middle punches and subtract them which will result in the amount of time they took for lunch:
"If @lunchflag = 1 then blahblahblah"
It's the "blahblahblah" part where I'm stuck. How can I tell the formula to only subtract the two "middle" punches?