SharePoint Date/Time Calculation Decision Tree

Question

I am trying to capture when emails are received and when they are completed in a SharePoint list to track how long it takes to complete an email. It includes the date and the time.

However, I do not want the column to count time after 5PM or before 8AM. So if "Date" is > than 5PM (Say "11/20/2020 7:00 PM"), I want it to auto correct to "11/20/2020 5:00 PM". If "Date" is < 8AM (Say "11/20/2020 4:00 AM"), I want it to auto correct to "11/20/2020 8:00 AM".

I'm am not even sure where to start with the formula because I do not know how to nest the if statements together. The other thing is, I Need it to keep the date portion and just change the time. So I do not know how to make the conditional statement only look at the time.

Thanks for your Help.

-Darren

Answer 1

You could try the below formula in the calculated column:

=IF(HOUR([date])>=17,TEXT([date],"mm/dd/yyyy")&" 5:00 PM",IF(HOUR([date])<8,TEXT([date],"mm/dd/yyyy")&" 8:00 AM",[date]))

Test result: