I have a PowerApps form which contains an adjustable "Submission Date" field. Here is a screenshot to show what I mean: enter image description here

I am trying to come up with a formula for a calculated column in SharePoint called "Submission Status" to determine whether or not the form was submitted during the day, which in my case would be between 9:30 AM to 5:00 PM (inclusive) on a weekday. I can get it to work for 9:00AM to 5:00PM, however I can't get it to work with a schedule starting at exactly 9:30AM. I'm fairly new to SharePoint so I'm not sure how to account for those minutes.

For reference, this is the formula I've got that works for 9:00AM - 5:00PM :

=IF(OR(HOUR([Submission Date])>17,HOUR([Submission Date])<9),"Submitted after hours",
IF(OR(TEXT([Submission Date],"dddd")="Saturday",TEXT([Submission Date],"dddd")="Sunday"),"Submitted on a weekend",
IF(OR(HOUR([Submission Date])<=17,HOUR([Submission Date])>=9),"Submitted during the day")))

Any help would be appreciated

1 Answer 1


You could use TEXT function like TEXT([SubmissionDate], "hh:mm") to accomplish this.

See the formula that worked for me. Please update Column name in the formula with the one that is in your list

=IF(OR(TEXT(SubmissionDate,"dddd")="Saturday",TEXT(SubmissionDate,"dddd")="Sunday"),"Submitted on a weekend",IF(AND(TEXT(SubmissionDate,"hh:mm")>="09:30:00",TEXT(SubmissionDate,"hh:mm")<="17:00"),"Submitted during the day","Submitted after hours"))

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.