Date is not recognized as date until cell has been edited

[Pitoun]

Hello,

I have a strange behaviour on my sheet.

I start with bouton "Planifier" on second tab called "Feuille2"
Then I enter user name Samia, select "Maladie" Type and add dates from 03.12.2018 to 05.12.2018 and press bouton "Ajouter"

This action well add a record after the last line into first tab called "input" (Input:B430:F430). Cell format is correctly set as "date" on cells D430:E430

Problem : on second tab called "Feuille2" should appear an M on december 3,4 and 5 (Feuille2:F69:H69)

Solution : on first tab called "input" I click to edit cell D430 [F2 key] and press [Enter] key. I do same on cell E430
Then the formula on second tab called "Feuille2" can read date and display an M in december correcponding cells.


I can't figure out why dates are not recognized as date until cell has been edited.

You need to download the file to get macro bouton working. Macro will not show if opened in a browser. My spreadsheet is available here.

Many thanks for any help

Pit

 

[James006]

Hello,

Are you getting your dates from a download ...?

Currently your dates are NOT Numbers ...but Text ...

Among the solutions... you can use TexttoColumns ... for corrective action ...

 

[Pitoun]

My dates are comming from the "Planifier" bouton where you plan collaborators absences.

So do you mean my macro is putting text in a date formated field ?

 

[Pitoun]

you can use TexttoColumns

what do you have in mind ? can you give me an example pls ?

 

[James006]

Hello,

To answer your question :

So do you mean my macro is putting text in a date formatted field ?

Yes ...

But you have to check the initial source ... since your macro only operates a copy ...

 

[Pitoun]

have to check the initial source ...

initial source comes from manual data entry form through bouton "Planifier" on the seconed tab called "Feuille2"

 

[James006]

Hello again,

Could you describe precisely ... " manual data entry " ...

1. With UserForm ... or directly into worksheet ?

2. As a complete date OR with Year Month Day separated ?

Sorry but ... the devil is in the details ...!!!

 

[Pitoun]

Hello again,

Could you describe precisely ... " manual data entry " ...
Sorry but ... the devil is in the details ...!!!

With a user Form that you can get by clicking bouton "Planifier" on second tab called "Feuille2"
There you got two input textbox. The value is then formated as dd.mm.yyyy

 

[Pitoun]

Here my code:



Private Sub ComboBox1_Change()


End Sub


Private Sub ComboBox2_Change()


End Sub

Private Sub CommandButton1_Click()
If ComboBox2 = "" Or ComboBox1 = "" Or TextBox3 = "" Or TextBox3 = "jj.mm.aaaa" Or TextBox4 = "" Or TextBox4 = "jj.mm.aaaa" Then
MsgBox ("toutes les informations ne sont pas rempli")
Else
If Sheets("Input").Range("b3") = "" Then
Sheets("Input").Range("b3") = ComboBox2
Else
Sheets("Input").ListObjects(1).ListRows.Add
End If


dlt = Sheets("Input").Range("d1048576").End(xlUp).Offset(1, 0).Row


Sheets("Input").Range("b" & dlt) = ComboBox2
Sheets("Input").Range("c" & dlt) = ComboBox1
TextBox3 = Format(TextBox3, "dd.mm.yyyy")
Sheets("Input").Range("d" & dlt) = TextBox3.Value
TextBox4 = Format(TextBox4, "dd.mm.yyyy")
Sheets("Input").Range("e" & dlt) = TextBox4.Value
Unload UserForm1

End If
End Sub


Private Sub TextBox3_AfterUpdate()
On Error GoTo messagerreur
TextBox3 = Format(TextBox3, "short date")
Exit Sub
messagerreur:
MsgBox ("le format introduit n'est pas valide, le format de date est jj.mm.aaaa!")
TextBox3 = Empty
End Sub


Private Sub TextBox3_Enter()
If TextBox3 = "jj.mm.aaaa" Then
TextBox3 = ""
End If
End Sub


Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox3 = "" Then
TextBox3 = "jj.mm.aaaa"
End If
End Sub


Private Sub TextBox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not (KeyAscii > 45 And KeyAscii < 58) Then
KeyAscii = 0
End If
End Sub


Private Sub UserForm_Initialize()
TextBox3.Text = "jj.mm.aaaa"
TextBox4.Text = "jj.mm.aaaa"


With ComboBox1
.AddItem "Maladie"
.AddItem "Vacances"
.AddItem "Récupération"
.AddItem "Personnel"
.AddItem "Demi-Journée"
.AddItem "Day Off"
.AddItem "Maternité"
.AddItem "Divers"
End With
End Sub
Private Sub textbox4_AfterUpdate()
On Error GoTo messagerreur
TextBox4 = Format(TextBox4, "short date")
Exit Sub
messagerreur:
MsgBox ("le format introduit n'est pas valide, le format de date est jj.mm.aaaa!")
TextBox4 = Empty
End Sub


Private Sub textbox4_Enter()
If TextBox4 = "jj.mm.aaaa" Then
TextBox4 = ""
End If
End Sub


Private Sub textbox4_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox4 = "" Then
TextBox4 = "jj.mm.aaaa"
End If
End Sub


Private Sub textbox4_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not (KeyAscii > 45 And KeyAscii < 58) Then
KeyAscii = 0
End If
End Sub

 

[James006]

Re,

Désolé d'avoir raison depuis le début ... :wink:

As expected your UserForm is built with TextBoxes ...

So your Date inputs are Text ... and NOT Numbers ...

Whenever, you are copying Dates back from your UserForm to your worksheet ... you do have to make sure your Texts are converted back to Numbers ...

[COLOR=#333333]Sheets("Input").Range("d" & dlt) = TextBox3.Value[/COLOR]
[COLOR=#333333]Sheets("Input").Range("e" & dlt) = TextBox4.Value[/COLOR]

should be replaced by :

[COLOR=#333333]Sheets("Input").Range("d" & dlt) = CDate(TextBox3.Value)[/COLOR]
[COLOR=#333333]Sheets("Input").Range("e" & dlt) = CDate(TextBox4.Value)[/COLOR]

Hope this will help