Wednesday, May 11, 2011

How to use calculated columns to assign a calendar Datetime span from a choice field

Say you have a production planning with guys working with 4 different schedules :
A. 7:30 to 16
B  9:30 to 17h30
... etc

With standard sharepoint, you have to create two dates (begining and end), select they day twice, and type the hours each time.

You want to only write a date and select the time span :

Resolution :
Create a choice (C_schedule), prefix each choice with "1", "2", "3", etc....
Create a date field (D_work_date, without time),
and two calculated columns of datetime result: DT_work_start, DT_work_end

You'll make sharepoint fill those columns with basically, the start date + the hour(s) of your working day.

The formulas will be

DT_work_start=D_work_date+IF(LEFT(C_Schedule)="1";"07:30:00";IF(LEFT(C_Schedule)="2";"07:30:00"; ..... )))

DT_work_end=D_work_date+IF(LEFT(C_Schedule)="1";"16:00:00";IF(LEFT(C_Schedule)="2";"17:30:00"; ..... )))

Et voila! :)

1 comment:

Emmanuel ISSALY said...

Doesnt work with recurring events, sadly. In that case, only the starting date is stored, the dates displayed by the reccuring event are calculated on the fly by the view. Thus a calculated field isn't going to work there. Any tips appreciated.