calculating time in Gallery and SubGallery

[Anonymous]

Hi,

I have one screen with two Gallery (Gallery and SubGallery), the first gal. is for tacking process and second for tasks. I need to calculate spend time on all task and sum time (in minutes) for all my tasks in selected Process gallery. All date source are SharePoint and I have Created field in both list. All task have same datatime of created and predefined order. When I mark as a checked the task I saved results from Now() in Finished field. I need to calculate time from field Created and field Finished in subGallery for all finished task, and sum time for main Gallery.

I try many diferent scenario but I don't success. Please help me.

[Drrickryp]

Hi @Anonymous,

You would use the Datediff() function for the time calculations. The syntax is

DateDiff( StartDateTime, EndDateTime [, Units ] )

• StartDateTime - Required. Starting date/time value.
• EndDateTime - Required. Ending date/time value.
• Units - Optional. The type of Units to add: Milliseconds, Seconds, Minutes, Hours, Days, Months, Quarters, or Years. If not specified, Days are used.

To get the sum from the subgallery you would use the Sum() in a label in the gallery with the datasource as the Items property of the subgallery and the formula for the time calculation separated by a comma.

[v-xida-msft · Accepted solution]

Hi @Anonymous,

Do you want to sum the spend time on all tasks in a selected Process in Process Gallery?

Based on the needs that you provided, I agree with @Drrickryp's thought almost. I think the DateDiff function could achieve your needs.

I have made a test on my side, please take a try with the following workaround:

Set the Text property of the Label control to following:

Sum(BrowseGallery1.AllItems, DateDiff(Created, Finished, Minutes))

On your side, you should type the following:

Sum(YourTasksGallery.AllItems, DateDiff(Created, Finished, Minutes)) /* <- Finished represents the Finished field in your SP list*/

In addition, if you only want to sum the spend time for all finished tasks, please take a try with the following article:

Sum(
    Filter(YourTasksGallery.AllItems, Finished <> Blank()),  /* <- Finished represents the Finished field in your SP list */
    DateDiff(Created, Finished, Minutes)
)

More details about the Sum function and DateDiff function in PowerApps, please check the following article:

Sum function

DateDiff function

Best regards,

Kris

[Anonymous]

Thank you guys for help,

I need to explain little bit more…. First, I have Process (main gallery) and all Processes have DIFERENT number of Tasks (SubGallery), second, when I checked one task as finished, next Task need to have start date-time same as end date-time of previous task (serial Process not parallel), third, i need to write Process duration (duration of all tasks) to sp list, not to calculate “in fly” because i need it for report (chart,…). In SharePoint I have two list: ProcesZaKlijenta (sorrey for Serbian name) which they have ID, IDProcess, Trajanje (duration), BrojKoraka (Num of task),… Second list KoraciZaProcesZaKlijenta which they have ID, IDProcesZaKlijenta, VremePocetaka (start date and time), VremeZavrsetka (End date and time), Trajao (duration),…

Finaly, all of above need to be on one screen and user only need to check which task is finished. Nothing else… no go from one screen to another and similar.

My the first problem is how to know Start Date&time of next task because all number of record in subGallery is different?

Second, How to Patch in both list in one action if I need to first patch only half of all field, patch different fields in another list based on patched field on previous list, ….

This is my code of check control in subGalery (OnCheck):

UpdateContext({_KzPzK: Patch(KoraciZaProcesZaKlijenta,ThisItem,{
    Zavrsen: true,
    VremeZavrsetka:Now(),
    VremePocetka:GalleryProcesZaKlijenta.Selected.PoslednjeVremePocetka,
    Trajao: DateDiff(GalleryProcesZaKlijenta.Selected.PoslednjeVremePocetka, Now(), Minutes)
    })
});
UpdateContext({_statusProcesa:If(GalleryProcesZaKlijenta.Selected.TrenutniKorak<>GalleryProcesZaKlijenta.Selected.UkupnoKoraka,"Pokrenut","Zavrsen")});
UpdateContext({_trajanje: GalleryProcesZaKlijenta.Selected.Trajanje});

Patch(ProcesZaKlijenta,GalleryProcesZaKlijenta.Selected,{
    Trajanje: _trajanje + _KzPzK.Trajao,
    PoslednjeVremePocetka:_KzPzK.VremeZavrsetka,
    TrenutniKorak:ThisItem.KzPzK_Redosled+1,
    Status:_statusProcesa
});

After all, i have problem with SharePoint latancy (i need to write to SP list and in next step in same action I need to use field from this list for paching another sp list). I don't want comment complete mess with dates and time format in diferent time zones 

If you have suggestion on my code, please be free to share with me... I am relativly new to PA.