24 Hour Schedule

[tapsmiled]

Hello,

Attached you will find a workbook that I created. It will allow my wife to enter the first date of her work week (F2) then in the G column, her schedule for each day. Her schedule values are E, M, L, X, VAC, P----each one of these is translated (B2:C8) as applicable times and a TITLE is created for each (Opening, Middle, Closing, etc.) in the D column. My intent is for her to enter the values, save it as a Comma Separted Values file then upload it to Outlook (and eventually her iphone if I can figure out how to do that). It will put her work schedule on her calendar and phone, basically, with minimal effort.

So now the question is, how could I apply this same function to a 24 hour schedule where some shifts begin at 11pm one night and finish at 7 am the following morning?

This is an example of the formula to translate an E, M, L value into a BEGINS time:
=IF($G2="E","08:00",IF($G2="M","12:00",IF($G2="L","14:00",IF($G2="X","",IF($G2="P","PERSONAL",IF($G2="VAC","VAC","")))))
This is eventually mapped to the appropriate value in the calendar. I got it to work as far as importing to the Outlook Calendar, but her schedule begins and ends on the same day, and I would like to be able to use it at my job that has around the clock scheduling.


Bonus points for anyone who can tell me how to sync Outlook to iCal.

[tapsmiled]

Thinking about this, would I have to create 2 columns, 1 for starting date and 1 for ending date, and base the calculation of starting date off of the possible start times? In other words, a person working ALPHA shift on 12/20/2013 would actually commence work at 2300 hrs on 12/19/2013 and finish the shift at 0700 on 12/20.

Understanding that the workbook as it is set up right now does not account for any of what i am asking...it is just a starting point that requires adapting: if I had the user type ALPHA into the G column (SHIFT) and it automatically populates the 2300 in the B column and 0700 in the C column, would it work if instead of just having the current A column for Start Date, I add a second column for end date? If so, what type of IF statement would I need to get the DATE (whatever is entered by the user)-1 day.

[tapsmiled]

Nevermind...
Talked it through myself and solved it.

=IF(B2="23:00",A2-1,A2) would work as a formula in an Ending Date column.