Hi Jeff G
I'm not sure if you got this figured out yet but if not this might work for
you.
Now I used this with a Calendar control but I think it should work for your
Date and Time Picker.
Here is my code:

Private Sub Calendar1_Click()

Range("A1").Value = Calendar1.Value

Range("A1").Offset(0, 1).Select
With Selection
ActiveCell.FormulaR1C1 = "=RC[-1]+1"
Selection.Copy
Range("C1:G1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A2").Select

End With

With Selection
..Value = Range("A1")
..NumberFormat = "mm/dd"
..Offset(0, 1).Select
End With

ActiveCell.FormulaR1C1 = "=RC[-1]+1"
Selection.Copy
Range("C2:G2").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Unload Me

End Sub


"Jeff G" wrote:

I am creating a customized timesheet for my company's owners. I have a
section which gives an overall date range that timesheet. I use a date and
time picker to pick the first date which is mm/dd/yyyy format. Then I use a
formula to automatically add six more days and report the ending date so the
overall date area looks as such: 12/26/05 to 01/01/06.

My problem arises when I go to a section of the timesheet which lists M, T,
W, R, F, Sa, Su with the abbreviated date below it such as 12/26 for M, 12/27
for T, 12/28 for W, etc.

For the date in Monday, I refer to the date from the date and time picker
cell from the top of the timesheet since it always is the Monday date for
that week. What I am having trouble with is in the fact that the format that
is used under my M is the same format as the date and time picker cell. So
instead of getting mm/dd, it inserts mm/dd/yyyy even though the format for
the cell under M states to use mm/dd.

Is there a way to override the format that is pulled from the date and time
picker cell?

Thanks,