• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problem for addition of Months and days using Date picker

syp1677

Member
Hello Gurus,
I have referred to following for incorporating date picker in my userform:

I have made a userform, which is meant to gather data of some chemicals, where user need to put manufacturing date using date picker and enter shelf life details. Expiry is calculated based on the input in shelf life and manufacturing date. So, whenever I enter date 1st of Mar'2023 i.e. 01-03-2023 and shelf life 1 months, I get output expiry date as 02-02-2023 instead of 01-04-2023.

My code is as follows:
Code:
Private Sub cmdSave_Click()

Dim lRow As Long
Dim c As String, p As String, f As String,
Dim e As Date

Dim ws As Worksheet

Set ws = Worksheets("Database")

'find first empty row in database
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1


p = frmdatainput4.txtshlflf.value ' numbers 1 to x'
c = frmdatainput4.cmblfunit.value ' getting unit eg.month, year or days'
e = frmdatainput4.txtmfgdt.value 'getting manufacting date'

            If c = "Months" Then
                f = Format(DateAdd("m", p, e) - 1, "DD-MM-YYYY")
                MsgBox "Modified date is " & f
            ElseIf c = "Days" Then
                f = Format(DateAdd("d", p, e), "DD-MM-YYYY")
            ElseIf c = "Weeks" Then
                f = Format(DateAdd("ww", p, e) - 1, "DD-MM-YYYY")
            ElseIf c = "Years" Then
                f = Format(DateAdd("yyyy", p, e) - 1, "DD-MM-YYYY")
            Else
                f = Format(e, "DD-MM-YYYY")
            End If
 
With ws
  .Unprotect Password:="123"
  .Cells(lRow, 1).value = frmdatainput4.cmbreagent.value
  .Cells(lRow, 2).value = frmdatainput4.txtmfgdt.value
  .Cells(lRow, 3).value = f
    .Protect Password:="123"
End With


'clear the data

frmdatainput4.cmbreagent.value = ""
frmdatainput4.txtmfgdt.value = ""
frmdatainput4.txtshlflf.value = ""
frmdatainput4.cmblfunit.value = ""

End Sub

Let me know where I might be wrong.
 
Hello, so check the involved variables values in particular c, p & e.​
It seems you forgot to convert the text box to a valid Excel date via the VBA function CDate for example …​
 
Thank you Mark for response.

I tried two things:
1. I change e = frmdatainput4.txtmfgdt.value to e = CDate(frmdatainput4.txtmfgdt.value)
It does not have any effect final results is same.

2. I incorporate CDate in if loop:
If c = "Months" Then
f = Format(DateAdd("m", p, CDate(e)) - 1, "DD-MM-YYYY")
MsgBox "Modified date is " & f
ElseIf c = "Days" Then
f = Format(DateAdd("d", p, CDate(e)), "DD-MM-YYYY")
ElseIf c = "Weeks" Then
f = Format(DateAdd("ww", p, CDate(e)) - 1, "DD-MM-YYYY")
ElseIf c = "Years" Then
f = Format(DateAdd("yyyy", p, CDate(e)) - 1, "DD-MM-YYYY")
Else
f = Format(e, "DD-MM-YYYY")
End If

Well both cases are not helping. Did you mean something else?

Regards, S
 
First check the variables values if they match at least with the expected in order to find out where the logic fails.​
According to VBA help as I wrote CDate must be obviously used with the text box only …​
 
So, Let me share what I did..
I added more variables to capture day and month and let it display in msgbox to understand the same.
f = format(e, "DD")
h = format(e, "MM")

So when I selected 1st of March'2023 of date picker.
Msgbox displayed f = 03 instead of 01 and h =01 instead of 03.

But I still do not understand how to resolve this and why its taking day instead of month.

I also tried with 28th Feb'2023, it worked completely fine.

Best Regards,
S.
 
Happens when the date Regional Settings is not respected.​
So rather than any guessing challenge which has no place in any Excel forum​
you should better share what is your Windows Regional Settings for dates​
and what exactly returns the date picker in which data type and if Text rather than Date in which date setting format …​
 
My Windows Regional Settings for dates:
83283

In the text box, when I select 1st of March'2023, it display 03/01/2023. I do not know how to determine the data type.

Best Regards,
S.
 
So it seems your date Regional Settings is 'dd-mm-yyyy' so what is exactly your textbox entry ?!​
 
As according to your date Regional settings the result should be 1-3-2023 for march 1st !​
As the code author warned it was made for US settings so find out another picker​
or transform its result according to your date settings …​
 
I do not know how to transform. What I am doing now is adding date picker to it instead of calculating it. Thanks Marc for understanding.

Best Regards,
S
 
Just obviously reversing day & month via the VBA text function Format to transform the date as expected like this demonstration :​
Code:
Sub Demo1()
    Const S As String = "3/1/2023"
    MsgBox Format(S, "mm-dd-yyyy")
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Back
Top