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,

Jeff,
If I understood your question, I got the result you required.

In A1 (format mm/dd/yyyy) I had 12/22/2005 and B1 I put "= A1" with B1
formatted as mm/dd - B1 contained 12/22. I got the same result using VBA.

Excel 2003.

"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,

Hello Jeff,

There are 2 ways to change the cell format. Manually or through code.
The manual method involves selecting the cell going to the menu bar and
selecting: Format || Cells... || Number (tab) . Under Category select
Date. You will see a list of predefined date formats.

The second way is through code. Say the cell in question is A1.
*Range("A1").NumberFormat = "mm/dd"*
This overides all other format conditions. You can also apply this to
multiple cells...
Range("A1:C10").NumberFormat = "mm/dd"

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=495605

Thanks for the feedback. Both you and Toppers referred me to use Format
Cells but that was the first thing I did and even though Format Cells shows
mm/dd, it still reports the cell in the same format as the date and time
picker format. If I change the date and time picker to mm/dd that is the
format that appears in the second cell as well. In other words, the format
from the date and time picker cell is forced on the second cell.

If A1 were the cell that had the date and time picker result, and B1 was a
referral to A1, the format to B1 always matches the format in A1 no matter
what format cell option I pick for B1.

How do I apply code to a particular cell such as was your second option?

Thanks

"Leith Ross" wrote:


Hello Jeff,

There are 2 ways to change the cell format. Manually or through code.
The manual method involves selecting the cell going to the menu bar and
selecting: Format || Cells... || Number (tab) . Under Category select
Date. You will see a list of predefined date formats.

The second way is through code. Say the cell in question is A1.
*Range("A1").NumberFormat = "mm/dd"*
This overides all other format conditions. You can also apply this to
multiple cells...
Range("A1:C10").NumberFormat = "mm/dd"

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=495605

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,