Appendix Thread. ( Codes for other Threads, etc.) Event Coding Drpdown Data validation

[DocAElstein]

In Support of this Post question
2020-02-15 15:08:06 https://excelribbon.tips.net/T009046...een_Dates.html Karim K.

Determining Differences Between Dates

From Allen Wyatt, here https://excelribbon.tips.net/T009046...een_Dates.html

….. When you are programming Excel macros, you should know that dates are stored internally, within variables, as serial numbers. The serial number represents the number of days elapsed since a starting "base date," specifically since 1 January 100. This means that you can perform math with the serial numbers, if desired. You can, for instance, find the number of days between two dates by simply subtracting the dates from each other.

If you want to get fancier in your date calculations, you can use the DateDiff function. This function allows you, for instance, to determine the number of weeks or months between two dates. In order to use the function to find this type of information, you would do as follows:

iNumWeeks = DateDiff("ww", dFirstDate, dSecondDate)
iNumMonths = DateDiff("m", dFirstDate, dSecondDate)

The first line determines the number of weeks between the two dates, and the second determines the number of months between them.
Remember that the DateDiff function is a macro (VBA) function, not a worksheet function. Excel handles a range of dates in worksheets that begin with January 1, 1900. In VBA, however, dates can begin (as already noted) in the year 100. That means that macros can handle a much larger range of dates, including dates prior to those handled natively by Excel……………..



Example: : User inputs "2/15/2019" in cell (C4) - The next day it shows "1 Day/s" and so on.


The following coding must go in the worksheets code module of the worksheet of interest:
_1 Right Click Tab _2 Select Show Code or _ 3 Double Click on worksheet in VB Editor project Explorer .JPG : https://imgur.com/1xcWkQJ , https://imgur.com/oWS0uZ4
Attachment 2748Attachment 2749


In first worksheet Code Module

Option Explicit ' https://excelribbon.tips.net/T009046_Determining_Differences_Between_Dates.html  '   https://docs.microsoft.com/de-de/office/vba/language/reference/user-interface-help/datediff-function
Public Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 3 Then
    Dim rngC As Range: Set rngC = Me.Range("C2:C" & (Me.UsedRange.Row + Me.UsedRange.Rows.Count - 1) & "") ' (Bottom left of Usedrange + Row count in UsedRange) - 1 will give us the last row
    Dim rngStr As Range
        For Each rngStr In rngC
            Debug.Print rngStr.Value ' From VB Editor, Hit keys  Ctrl + g  to see the immediate window
            If rngStr <> "" Then
            Dim Vl As String: Let Vl = rngStr.Value
                If Len(Vl) < 8 Then MsgBox Prompt:=Vl & " is too short for a date": GoTo Nxt
                If Len(Vl) - Len(Replace(Vl, "/", "")) <> 2 Then MsgBox Prompt:="Don't have 2 ""/""s in " & Vl: GoTo Nxt
            Dim Dey As String, Munf As String, Jear As String
            Dim strSplt() As String: Let strSplt() = Split(Vl, "/", 3, vbBinaryCompare) ' https://imgur.com/1xcWkQJ
             Let Dey = strSplt(1): Munf = strSplt(0): Jear = strSplt(2)
            Dim Dte As Date, strDte As String, LngDte As Long
             Let strDte = Format(Dey & " " & Munf & " " & Jear, "dd mmmm yyyy"): Debug.Print strDte
             Let Dte = CDate(strDte)
             Let strDte = Format(Dey & " " & Munf & " " & Jear, "dd" & ", " & "mmmm" & ",  " & "yyyy"): Debug.Print strDte
             Let LngDte = CLng(Dte) ' Allen Wyatt: When you are programming Excel macros, you should know that dates are stored internally, within variables, as serial numbers. The serial number represents the number of days elapsed since a starting "base date," specifically since 1 January 100. This means that you can perform math with the serial numbers, if desired. You can, for instance, find the number of days between two dates by simply subtracting the dates from each other.
            Dim LngNow As Long: Let LngNow = CLng(Now())
            ' https://excelribbon.tips.net/T009046_Determining_Differences_Between_Dates.html  '  https://docs.microsoft.com/de-de/office/vba/language/reference/user-interface-help/datediff-function
            Dim iNumDays As Long, iNumWeeks As Long, iNumMonths As Long
             Let iNumDays = DateDiff("d", LngDte, LngNow) ' = LngNow-LngDte
             Let iNumWeeks = DateDiff("w", LngDte, LngNow)
             Let iNumMonths = DateDiff("m", LngDte, LngNow)
             Let Application.EnableEvents = False
             Let rngStr.Offset(0, 1).Value = iNumDays & " Days,  " & iNumWeeks & " Weeks,   and " & iNumMonths & " Months."
             Let rngStr.Offset(0, 2).Value = strDte
             Let Application.EnableEvents = True
            Else ' case empty cell
            End If
Nxt:    Next rngStr
    Else ' No change in column 3 ( "C" )
    End If

Me.Columns.AutoFit
End Sub

Note:
You may need to adjust the coding a bit with a +1 or -1 somewhere to get the day count output exactly as you want it


The above macro will start automatically when you add a date into column “C” , provided it has this sort of format
2/15/2020
( Month/Day/Year )


The following additional macro, will ensure that the worksheet is updated when the workbook is opened

Macro in ThisWorkbook code module

Private Sub Workbook_Open()
 Call Tabelle1.Worksheet_Change(Worksheets.Item(1).Range("C2"))
End Sub

The above code module and coding therein can be seen by double clicking on the ThisWorkbook code module in the VB Editor explorer:
Double Click on ThisWorkbook in VB Editor Explorer.jpg : https://imgur.com/Kls33SD
Attachment 2747

Note, In order to call our macro Public Sub Worksheet_Change(ByVal Target As Range) in this way, we have changed the more typically seen , default option of Private to Public in the first macro in the worksheets code module

Here is a typical output
_____ Workbook: KarimKAllenWyattDateDifferences.xlsm ( Using Excel 2007 32 bit )

Row\Col	B	C	D	E	F
3
4		2/15/2020	2 Days, 0 Weeks, and 0 Months.	15, Februar, 2020
5		1/15/2020	33 Days, 4 Weeks, and 1 Months.	15, Januar, 2020
6		6
7		3/12/2019	342 Days, 48 Weeks, and 11 Months.	12, März, 2019
8		2/16/2020	1 Days, 0 Weeks, and 0 Months.	16, Februar, 2020
9		z

Worksheet: Tabelle1

---

KarimKAllenWyattDateDifferences.xlsm : https://app.box.com/s/ti0n1wj62hcd2qmhcg5kiqle1sya79ux