Date format Issue

[sheldon]

Hi,

I am entering a date into a sheet via a formula within the worksheet. I have formatted the cell as Date, but when the the date is entered into the cell it changes format to text and the date is displayed as a number e.g. 360608.

The formula is If .Value <> "" Then
Me.Cells(.Row, "L").Formula = Replace(ADD_FORMULAa, "<rownum>", .Row)
End If
The selected date is from a combo box.

Do you know how I would make the formula enter the date, in the format Date?

Hope this is enough information.

Thanks

[Kenneth Hobs]

[VBA]
Sub FDate()
Range("A1").Value = Date
Range("A1").NumberFormat = "mmmm dd, yyyy"
End Sub[/VBA]

[Bob Phillips]

[vba]

If .Value <> "" Then
Me.Cells(.Row, "L").Formula = CDate(Replace(ADD_FORMULAa, "<rownum>", .Row))
End If
[/vba]

[sheldon]

Hi,

When I enter your formula changes xld, it no longer enters the date into the form, any ideas?

Thanks

[Bob Phillips]

sheldon,

I am struggling to see what you mean, and what the issue is. I had to concoct a test, and that worked for me (no surprises there!), but maybe my concoction was wrong.

Can you post a sample workbook, so we can get it right?

[sheldon]

I have attached a sample document. Hope this is enough information. There is code in the worksheet.

Column "L" is formatted at Date, but when the Worksheet enters the date it changes the format to Text and the date is shown as displayed.

Thanks

[Bob Phillips]

sheldon,

that is nothing liek what you have posted. There is no combobox, if you enter a value in L, it bypasses the code as that code tsets for column A>

Have you got a real example?

[Kenneth Hobs]

Your problem is that Excel is changing the numeric format to Text. This is because the first cell in the formula was formatted as Text even though it appears to be a date. So, fix the formats for the Column L cells to Date or set the NumberFormat after you set the formula to force a date format as I demonstrated earlier.
[VBA]Me.Cells(.Row, "L").Formula = Replace(ADD_FORMULAa, "<rownum>", .Row)
Me.Cells(.Row, "L").NumberFormat = "mm/dd/yyyy"[/VBA]

[sheldon]

Thanks Kenneth.

That code works lovely.

Do you know if there is a way of inserting conditional formatting into this code?

Thanks

[Kenneth Hobs]

Sure. Just record a macro and then adapt it. Post your recording if you need help.

[sheldon]

[vba]Sub Con1()
' Con1 Macro
' Macro recorded 10/09/2008 by
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(TODAY()>=K281+8)"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=(TODAY()<=K281+8)"
With Selection.FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 50
End With
End Sub[/vba]

This is my macro. How would i configure it to be entered into the worksheet, every time a new entery is entered? (So that every new cell in column K has this formatting)

Thanks

[Bob Phillips]

[vba]

With Me.Cells(.Row, "L")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=(TODAY()>=K" & .Row & "+8)"
With .FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=(TODAY()<=K" & .Row & "+8)"
With .FormatConditions(2).Font
.Bold = True
.Italic = False
.ColorIndex = 50
End With
End With
[/vba]

[sheldon]

Thanks for all the help.

When I enter a date into the cell, it changes it into American format, but I am wanting English (dd/mm/yyyy). Do you know how I would do this?

My code so far...

[vba]Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:A" '<== change to suit
Const ADD_FORMULA = _
"=IF(M<rownum><>"""",""Returned"",IF(AND(TODAY()>=K<rownum>+8,M" & _
"<rownum>=""""),""Require Chasing"",""No Action Required""))"
Const ADD_FORMULAa = "=K<rownum>+8"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

If .Value <> "" Then

Me.Cells(.Row, "N").Formula = Replace(ADD_FORMULA, "<rownum>", _
.Row)

End If
If .Value <> "" Then
Me.Cells(.Row, "L").Formula = Replace(ADD_FORMULAa, "<rownum>", _
.Row)
Me.Cells(.Row, "L").NumberFormat = "dd/mm/yyyy"
End If

End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

[Bob Phillips]

which cell is changing to US format sheldon?

I just tried it, and it put a UK date in K. If I put a date in A, that stays UK style.

[sheldon]

Column K and L, which are both dates entered by the form, change format to US.

[Bob Phillips]

Column L doesn't have a date, it gets a text value!

[sheldon]

Sorry meant to say column M

[Bob Phillips]

Where does M come into it, that is not in the code. Are you entering that directly?

[sheldon]

Ok, i'll explain the columns.

Column K = Date a document is sent and entered via a combo box on a form.
Column L = Date a document is required and is a formula be automatically display 8 days after column K.
column M = Date a document is returned and is entered via a combo box on a form.
Column N = is a formula entered via the code.

thanks

[sheldon]

Both dates that are being entered directly are being changed into US dates.