Power Platform Community Forum Thread Details

I have previously created a topic about this but no one got back to me after the first reply so therefore posting again and hoping someone can help me. Basically I am creating an app for employees to enter their time into which once the week is collected that data is sent to a SharePoint list. We want their hours calculated based on their start and finish time. When an employee enters their hours their lunch break (30mins) is taken off their hours and their break (15mins) is added on to be correct, however some employees work on several projects in one day. Once they submit time for a date it is added to a collection 'colTimeEntryWeek' My problem is once an employee has already submitted time for a day when they try to submit time spent on another project for the same date I don't want their break and lunch break hours to be added on / taken off their hours again as they already will have had that adjusted by their first entry for that day.

My initial formula to get their hours with break and lunch break taken into account is:

If(
 Or(cbxProjectSTE.Selected.Value = "Day Off", cbxProjectSTE.Selected.Value = "Off Sick"), "0",
 Or(cbxProjectSTE.Selected.Value = "Holiday", cbxProjectSTE.Selected.Value = "Travel Day"), "9.5",

With({StartFinishDiff:
 DateDiff(
 TimeValue(drpStartTimeSTE1.Selected.Value & ":" & drpStartTimeSTE2.Selected.Value),
 TimeValue(drpFinishTimeSTE1.Selected.Value & ":" & drpFinishTimeSTE2.Selected.Value) - TimeValue(txtLunchBreakSTE) + TimeValue(txtBreakSTE), Minutes)},
With({MinDiff: Mod(StartFinishDiff, 60)},
With({MinAdjustment: If(Mod(MinDiff,15)>7, 15-Mod(MinDiff, 15), -Mod(MinDiff, 15))},
((StartFinishDiff - MinDiff)/60 + Text( (MinDiff + MinAdjustment)/15 * .25,"[$-en-US].0#"))))))

Now trying to figure out how to write formula so that if they are submitting more time for this date it checks my collection to see if there is already time on this date and if there is then don't adjust hours by taking away their lunch break time and adding their break time. If there isn't any time on this date then do the calculation. I am trying the below:

If(
 Or(cbxProjectSTE.Selected.Value = "Day Off", cbxProjectSTE.Selected.Value = "Off Sick"), "0",
 Or(cbxProjectSTE.Selected.Value = "Holiday", cbxProjectSTE.Selected.Value = "Travel Day"), "9.5",

 If(
 IsEmpty(
 LookUp(colTimeEntryWeek, Date_ = datDateSTE.SelectedDate)),

With({StartFinishDiff:
 DateDiff(
 TimeValue(drpStartTimeSTE1.Selected.Value & ":" & drpStartTimeSTE2.Selected.Value),
 TimeValue(drpFinishTimeSTE1.Selected.Value & ":" & drpFinishTimeSTE2.Selected.Value), TimeUnit.Minutes)},
 
With({StartFinishDiff:
 DateDiff(
 TimeValue(drpStartTimeSTE1.Selected.Value & ":" & drpStartTimeSTE2.Selected.Value),
 TimeValue(drpFinishTimeSTE1.Selected.Value & ":" & drpFinishTimeSTE2.Selected.Value) - TimeValue(txtLunchBreakSTE) + TimeValue(txtBreakSTE), TimeUnit.Minutes)},
With({MinDiff: Mod(StartFinishDiff, 60)},
With({MinAdjustment: If(Mod(MinDiff,15)>7, 15-Mod(MinDiff, 15), -Mod(MinDiff, 15))},
((StartFinishDiff - MinDiff)/60 + Text( (MinDiff + MinAdjustment)/15 * .25,"[$-en-US].0#"))))))))

It's not quite right as it works fine for first submission hours are calculated correctly but if I try to enter more information on this date the hours are just blank.

Please help me find where I have went wrong or a better way to do it. I am struggling and desperate for a resolution. If anyone needs more info please let me know.

Categories:

Building Power Apps