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") 

alternatively

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

=DateDifference("date1","date2","units") 
=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(MONTH(NOW),"DDDD") 
=DATE(YEAR(NOW()),MONTH(NOW()),1)
=eomonth(today(),-1)+1
=TEXT(TODAY()-DAY(TODAY())+1,"ddd, mmm dd, yyyy")
=TEXT(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1)),"dddd")




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