Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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")))
share|improve this answer
    
Great! The Labels were a little mixed up (Night was in the spot of Day, etc.) But that worked. Thank you. –  user38046 Mar 17 '10 at 23:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.