PDA

View Full Version : Date format Issue



sheldon
09-08-2008, 06:41 AM
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
09-08-2008, 07:20 AM
Sub FDate()
Range("A1").Value = Date
Range("A1").NumberFormat = "mmmm dd, yyyy"
End Sub

Bob Phillips
09-08-2008, 07:33 AM
If .Value <> "" Then
Me.Cells(.Row, "L").Formula = CDate(Replace(ADD_FORMULAa, "<rownum>", .Row))
End If

sheldon
09-09-2008, 12:57 AM
Hi,

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

Thanks

Bob Phillips
09-09-2008, 01:49 AM
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
09-09-2008, 02:34 AM
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
09-09-2008, 02:54 AM
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
09-09-2008, 05:29 AM
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.
Me.Cells(.Row, "L").Formula = Replace(ADD_FORMULAa, "<rownum>", .Row)
Me.Cells(.Row, "L").NumberFormat = "mm/dd/yyyy"

sheldon
09-10-2008, 03:46 AM
Thanks Kenneth.

That code works lovely.

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

Thanks

Kenneth Hobs
09-10-2008, 05:09 AM
Sure. Just record a macro and then adapt it. Post your recording if you need help.

sheldon
09-10-2008, 06:13 AM
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

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
09-10-2008, 06:56 AM
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

sheldon
09-11-2008, 01:08 AM
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...

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

Bob Phillips
09-11-2008, 01:14 AM
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
09-11-2008, 05:31 AM
Column K and L, which are both dates entered by the form, change format to US.

Bob Phillips
09-11-2008, 05:57 AM
Column L doesn't have a date, it gets a text value!

sheldon
09-11-2008, 06:04 AM
Sorry meant to say column M

Bob Phillips
09-11-2008, 07:05 AM
Where does M come into it, that is not in the code. Are you entering that directly?

sheldon
09-11-2008, 07:13 AM
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
09-11-2008, 07:14 AM
Both dates that are being entered directly are being changed into US dates.

Bob Phillips
09-11-2008, 07:18 AM
How about if we brute-force it?



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

With Target

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

If .Value <> "" Then

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

Else

If IsDate(.Value) Then

.Value.NumberFormat = "dd/mm/yyyy"
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

sheldon
09-11-2008, 07:25 AM
No, that doesn't make a difference. Still entering a US date.

Bob Phillips
09-11-2008, 07:31 AM
Can you post that workbook?

MaximS
09-11-2008, 02:53 PM
try this after entering your data into place:

this code will convert date form dd.mm.yyyy into english format dd/mm/yyyy


'Calling out date converting function
Dim Cur_Rng As String
Dim ShtName As String
Dim temp As String

' Range which needs to be converted into english date
Range("A2").Select

Cur_Rng = Range(Selection, Selection.End(xlDown)).Address
ShtName = ActiveSheet.Name
temp = Change_Date(Cur_Rng, ShtName)
If temp <> 0 Then GoTo Er_Fin
Range("A2:A" & LRow).NumberFormat = "dd/mm/yyyy;@"
Application.CutCopyMode = False
Application.CommandBars("Stop Recording").Visible = True


You will also need that function


Function Change_Date(Adr As String, ShtName As String)

''''''''''''''''''''''''''''
' Date conversion function '
''''''''''''''''''''''''''''
Dim Cur_Sht As String
Dim Cur_Cell As String
On Error GoTo Err_End
Cur_Sht = ActiveSheet.Name
Cur_Cell = ActiveCell.Address
Sheets(ShtName).Select
Range(Adr).Select
Selection.Replace What:=".01.", Replacement:=" Jan ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=".02.", Replacement:=" Feb ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=".03.", Replacement:=" Mar ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=".04.", Replacement:=" Apr ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=".05.", Replacement:=" May ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=".06.", Replacement:=" Jun ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=".07.", Replacement:=" Jul ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=".08.", Replacement:=" Aug ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=".09.", Replacement:=" Sep ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=".10.", Replacement:=" Oct ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=".11.", Replacement:=" Nov ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=".12.", Replacement:=" Dec ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Change_Date = 0
GoTo Fin
Err_End:
Change_Date = Err.Number
MsgBox "change Date system encountered error: " & Err.Description, _
vbCritical + vbOKOnly, "Fatal Error!"
Fin:
Sheets(Cur_Sht).Select
Range(Cur_Cell).Select
End Function

sheldon
09-12-2008, 06:56 AM
Hi,

Sorted the date problem.

Just to go back to to the condition formatting. The text is always in red and doesn't change if the date is altered. Any ideas?

Thanks again for the help!

Bob Phillips
09-12-2008, 08:37 AM
What was the problem?

What conditional formatting?

sheldon
09-15-2008, 01:49 AM
Hi,

I recorded this macro which used condition formatting on the cell entered with a date...


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

You kindly replied with a code to be inputted...

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


But the formatting turns the cell red and doesn't alter when the date cell is changed.

thanks