VBA Code

To convert a date string to a date value ActiveCell.formulaR1C1 = Activecell.Value.
Using the .Find method to search for dates is not that straight forward
The date you are searching for must be in the default format e.g. 01/07/1996

Set finddate = range("A1:A200").Find (what:="01/07/1998") 


Set finddate = range("A1:A200").Find (what:=DateValue("01 07 1998"), lookin:=xlFormulas) 

Custom VBA Functions

Function to returns the number of days, months or years between 2 dates

=TimeDifference("time1","time2","units") - mins, secs or hours

or have multiples of both (i.e. 1 day 13 hours)

Functions that calculates the first day of the current month

=TEXT(TODAY()-DAY(TODAY())+1,"ddd, mmm dd, yyyy")

This uses the format "Long Date" and "Medium Time" - this makes sure that it works regardless of international settings.
You could use a different format

TheDate = Format(Date, "dd/mm/yyyy") 

Sub DateAndTime() 
' Displays the current date and time

    TheDate = Format(Date, "Long Date")
    TheTime = Format(Time, "Medium Time")

' Determine greeting based on time
    Select Case Time
        Case Is < 0.5: Greeting = "Good Morning, "
        Case Is >= 0.7083: Greeting = "Good Evening, "
        Case Else: Greeting = "Good Afternoon, "
    End Select

' Append user's first name to greeting
    FullName = Application.UserName
    SpaceInName = InStr(1, FullName, " ", 1)
' Handle situation when name has no space
    If SpaceInName = 0 Then SpaceInName = Len(FullName)
    FirstName = Left(FullName, SpaceInName)
    Greeting = Greeting & FirstName

' Show the message
    MsgBox TheDate & vbCrLf & TheTime, vbOKOnly, Greeting
End Sub

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrev