0

I am working with SharePoint Calendar list to create a shift schedule for a team (End user side of things, I am not writing code). I have added a few custom columns to the Calendar List Item. I have the list exported to excel where I have a Pivot table set up so I can see a summary of the different columns - I can see the person's name (From the title column), total hours they are scheduled for (separated into weekdays and weekends based on a custom column I added).

What I need is a way to check the start time of the shift to determine if it is a Day shift (starts at 7am), Eve shift (starts at 3pm), or a Night shift (starts at 10pm). So, when creating a new calculated column I would assume the function I need would go something like "=If([StartDate]>...." but I am not sure on the rest. Anyone know how I would write that function?

1 Answers1

0

You'll need to extract just the hour from your StartDate field, and then compare against that. The Hour() function pulls the hour as number from 0-23. Then you nest multiple IF() statements and you have your answer. Something like this should work:

=IF(HOUR([StartDate])<6,"Night",IF(HOUR([StartDate])<14,"Day",IF(HOUR([StartDate])<21,"Eve","Night")))
MattB
  • 11,194
  • 1
  • 30
  • 36
  • Great! The Labels were a little mixed up (Night was in the spot of Day, etc.) But that worked. Thank you. –  Mar 17 '10 at 23:56