calendar with multiple drop down in one cell

[fonsy]

Hi, I'm new here so please be kind...

So, I'm trying to create a calendar where I will have the whole month of lets say "April" and in one cell I need time in and time out separated by a comma, with one hour of lunch, which won't count towards the total of added hours for the day. It is easy to figure lets say 9:00am to 5:00pm = 8 hours, but it should be 7 because of the hour for lunch. I don't need it to say "time in and time out".

Ps* I'm using drop down list for making things easier once is done.

As for now I used this formula to substract the hour:
=MAX(0,MOD(D2-C2,1)-IF(MOD(D2-C2,1)>5/24,"1:0"))

... it worked fine if I have two different cells running the function, but it won't work if the two times are sitting in just one cell. It gives me a decimal value when I need h:m. I tried to fix that by formating the cells to hours, but still wont' work.

I used this code in order to add the two values in one cell:


Dim oldVal As String
Dim newVal As String

If Target.Address(0, 0) <> "C7" Then Exit Sub
On Error GoTo ReEnable
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If oldVal <> "" And newVal <> "" Then
Target.Value = oldVal & ", " & newVal
End If
ReEnable:
Application.EnableEvents = True

End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

So basically this is what I'm trying to accomplish:
Fonsy 9:00, 5:00 <----- in one cell selected from the drop down menu; total hours = 7 in a different cell.

I'm not an expert on excel, so I've been running into a wall and it kind of hurts..

Thank you for your help
fonsy

 

[fonsy]

I'm using EXCEL 2010. I forgot to mention that in the previous post.

Thank you again!