Need help to solve the issue in total working hours calculation in PowerApps

[B-Pooja]

I have two 'date picker' in power apps named as "Start date and time" and "End date and time" and have 1 'Break in mins' card which have the data type as "Number". The source data for this app is in SharePoint List. I created calculation for Total working Hours. Here I attached the Calculation,

Now my Query is, When I submit this form in PowerApps  the data is stored in SharePoint list but the PowerApps throws an error message like" The value ":" cannot be converted to a date or time value " How to resolve this issue in Power apps

[SanshubhLubal]

Hi @B-Pooja ,

I've done same as @v-xiaochen-msft and it's working fine in Mine

[SanshubhLubal]

And also I've done on dates also

With(
    {
        minutesbetween: DateDiff(
            DateValue2.SelectedDate + Time(
                Value(HourValue2.Selected.Value),
                Value(MinuteValue2.Selected.Value),
                "00"
            ),
            DateValue3.SelectedDate + Time(
                Value(HourValue3.Selected.Value),
                Value(MinuteValue3.Selected.Value),
                "00"
            ),
            Minutes
        ) - DataCardValue24.Text
    },
    With(
        {
            hoursLeftover: Mod(Text(minutesbetween/60,"0"),24),
            minutesLeftover: Mod(minutesbetween,60)
        },
        If(
            minutesbetween/ 60 > 24,
            Text((minutesbetween / 60)/24,"0") & " Days and " & hoursLeftover & " Hours and " & minutesLeftover & " Minutes",
            (minutesbetween - minutesLeftover) / 60 & "Hours and " & minutesLeftover & " Minutes"
        )
    )
)

[B-Pooja]

Hi,
  I didn't know that what I did. When I open my form it shows me the error message " The value ":" cannot be converted to date or time value" also when I open the gallery page again it shows me the error message. How to resolve this issue.

[SanshubhLubal]

1. You can write my code, my code is different from @v-xiaochen-msft 's code.
2. And use this code on the label's text property.

3. Where you're getting this error on which controls which property? Tell me in detail.

[Ramole]

Hi @B-Pooja 
I have tested your code at first it was throwing an error, I don't know what I have changed but it worked try it 

With(
    {
        minutesbetween: (DateDiff(
            TimeValue(HourVaLue.Selected.Value & ":" & MinuteVaLue.Selected.Value),
            TimeValue(HourVaLue2.Selected.Value & ":" & MinuteVaLue2.Selected.Value),
            Minutes
        ) - Value(DataCardVaLue6.Text))
    },
    With (
        {
            minutesLeftover: Mod (
                minutesbetween,
                60
            )
        },
        (minutesbetween - minutesLeftover) / 60 & " Hours and " & minutesLeftover & " Minutes"
    )
)

 

[B-Pooja]

Hi @SanshubhLubal , @Ramole ;
I tried both your codes, it was successfully submitted. and stored in SharePoint. But that issue remains same.
I have SharePoint list as 3 dates,
              *Date - Date and Time Datatype.
              *Start Date - Date and Time Datatype.
              *End Date - Date and Time Datatype.
In PowerApps I have 3 Screens.
            *Home Screen
            *Add Record (Edit Form)
            *View Record. (Gallery and View Form)
When I play the Power app. Move to Add record, It shows the below issue.

In Add Record screen, The default mode = Edit , When I play the app, the form shows " No items to Display"
In Add Record screen, The default mode = New , It shows the error " The value ":" cannot be converted to a date or time value.
When I move to another screen also " It shows the error " The value ":" cannot be converted to a date or time value.

Thanks,
Pooja

[B-Pooja]

Hi @Ramole @SanshubhLubal @v-xiaochen-msft 
I solved the above noted issue. Thank you for your responses.

Thanks,
Pooja

[SanshubhLubal]

Then marked this issue as solved.
and what was the issue

[Ramole]

@B-Pooja 

Can you let’s us know how you solved your issue by which solution?

[B-Pooja · Accepted solution]

Hi @Ramole @SanshubhLubal 
 In SharePoint "Working Hours" column data type as  'Single line of Text'.
In Edit form I  added the working hours column. I just set the visible as 'false' for the  working hours data card value instead I added label box in that default property I added the working hours calculation.