DateDiff and Hours function throwing errors

[Anonymous]

I have a formula here for calculating the date difference between two date pickers and an hours and minutes dropdown for each date picker, calculating the difference in hours.

This is the formula in use: 

DateDiff(
txtStartDate.SelectedDate + Time(Value(StartHour.SelectedText.Value), Value(StartMinute.SelectedText.Value), 0),
txtEndDate.SelectedDate + Time(Value(EndHour.SelectedText.Value), Value(EndMinute.SelectedText.Value), 0), Hours)

For some reason, the Hours keyword is throwing two errors:

"The function DateDiff has some invalid arguments."

"Invalid argument type (Number). Expecting a Text value instead."

Days, Milliseconds, Seconds and Years works fine in this formula but not Hours.

Any help would be greatly appreciated.

Thanks!

[RandyHayes]

@Anonymous 

Strange.  I'm not able to replicate that situation.

A couple things to look at.  

1) Replace the SelectedText in your formula to Selected.  SelectedText has been deprecated and should be avoided.

2) Look at the Items properties of your DropDowns.  Check for any differences in the values you are providing to the dropdown.

Doesn't really hit the target for the problem, but perhaps will help in finding a clue.

[Anonymous]

@RandyHayes 

Thanks for your reply.

I changed SelectedText to Selected and the errors being thrown are still the same. The DropDown Items are ok and all values are as they should be.

I will provide more details below:

One thing to note as I know when this error first appeared. The resulting hours difference is being added to a SharePoint list as the database. I was adding two new columns for hours and minutes in the list. Once I added these columns, the error began to throw. However, I did not add any DataCards for these new columns on PowerApps, but it is the turning point in my app between this formula working and not working.

Also, the formula works fine when used on a new Screen, so it must be a problem with the Screen in use at the moment.

I hope these extra details may help find the issue.

Thanks again. 

[v-yutliu-msft]

Hi @Anonymous ,

I've made a similar test but not met the same problem like your description.

Please check these points:

1)the data type of the filed  that uses this formula to update is number.

DateDiff will result a number value not text value.

2)the columns' name that you added later have no ambiguity.

If the problem still exists, could you show me more about your settings or pictures?

Best regards,

Community Support Team _ Phoebe Liu

[RandyHayes]

@Anonymous 

So, I think we are getting somewhere when you say that the problem appears to be on that screen you're working on.

I would take a look at the differences between the working and non-working screen.  Pay attention to data type (text or number).  Make sure the controls you expect you are referencing in your formula are the ones you really want.

Feel free to post any formulas, screenshots or other items that might help should you need more.

[Anonymous]

Hi @v-yutliu-msft,

Thanks for the reply!

1) The data type of the column is in number format so that doesn't seem to be the problem.

2) I have since deleted these two columns and am not using them. I am recording to the original hours and minutes calculation to a column called "Total Hours". This worked perfectly until I added two new columns to the SharePoint list.  

Here is a screenshot of the error. As I said, it works fine with the Minutes keyord but not Hours. I have listed the error descriptions up above. The DateDiff function is inside the Total Hours textbox in the 'Default' section.

Hopefully, this helps and gives you an idea of why or how this is happening as I really don't know.

Thank you again.

[RandyHayes]

@Anonymous 

This is very odd... I have the exact same formula and setup to look this over for you and there is no error, seems to work fine.

If you change the formula to be Minutes instead of Hours - You state that it works fine? (except obviously wrong) And then if you re-type Hours, you then get the error?

One other thing to try is to start to hard-code some items to test this.  Replace the Hour and Minute values with actual numbers instead of the DropDown values.  Remove the Time functions.  

See if any of those might show where things go wrong.

[Anonymous · Accepted solution]

Hi @RandyHayes,

So I decided to create a new SharePoint and migrate the app's database to this new list. Upon doing this, the formula began working fine. I may have corrupted the original SharePoint list once I added two new columns.

I think I will work with the new SharePoint list, but it is unfortunate we can not figure out the issue.

Thank you very much for all your help!

[RandyHayes]

@Anonymous 

I understand...sometimes you need to clobber and move on.

I definitely was intrigued to get to the issue.  Maybe save it for a rainy day 😉

Good luck with the rest and post back if you run into more issues.