How to work with date time python functions?


vba excel modify dates #DateAdd #DateValue #DateSerial #DateDiff

How to work with date time python functions. As with most programming languages date and time functions can be a bit tricky sometimes hence why we in this post we will go through the difference between the available functions. In python, a date is not a datatype of its own which is why we need to import libraries with date functionality.

Overview

There are 5 classes in the Datetime library for working with dates and times.

  • Date --> manipulate dates. Meaning year, months, days.

  • Datetime --> for working with both dates and times.

  • Time --> manipulate time. Meaning Hours, minutes, seconds.

  • Timedelta --> great for manipulating dates and times.

  • Tzinfo --> for dealing with timezones.


Below we go through most of your Date needs *not too be confused with dating needs*


The most practical way to work with dates in python is to import the library "datetime" with the following command.

Import datetime

Current date time can be displayed with the following:

x = datetime.datetime.now()
print(x)
#Result --> yyyy-mm-dd hh:mm:ss:ms
where: 
        yyyy = year
        mm = month
        ss = seconds
        ms = microseconds

If we want to display the current year, month or day we can just type a "." after x like the below:

x = datetime.datetime.now()
print(x.year)
print(x.month)
print(x.day)

Create a date

x = datetime.datetime(2027, 10, 21)
print(x)
#Result --> 2027-10-21 00:00:00
#note that hours, minutes and microseconds are 0 by default if not specified.

Date conversion with strftime()

Strftime is a practical function for converting the date time variable to useable components such as weekday, day, month etc.

print(x.strftime("%A")) #Sunday
print(x.strftime("%w")) #weekday number 0-6, where 0 is Sunday.
print(x.strftime("%d")) #day of the month.

Example for 2027-10-21 11:07:05
Syntax        Explanation            Result

%y            Year                   2027

%b            Month, short           Oct
%B            Month, long            October
%m            Month, number          10

%a            Weekday, short         Thu
%A            Weekday, long          Thursday
%w            Weekday, number        4
%d            Day of the month       21

%H            Hour, 24h format       11
%I            Hour, 12h format       11
%p            AM/PM                  AM

%M            Minute                 7
%S            Second                 5
%f            Microsecond            N/A, not specified above.

%U            Week number.           42
              (start on Sunday)
%W            Week number.           42
              (start on Monday)                      

Convert text to datetime format.

Now that we know how to work with datetime formats, it is probably also useful to know how to convert text to a datetime format in a quicker way than breaking up the text parts of for example "2027-10-21".


For this exercise we can use the "fromisoformat" function.

from datetime import date

print (date.fromisoformat('2027-10-21')) 
#Result --> 2027-10-21

Time functions

Time is also a useful library which can similarly be imported with:

import time

Print the local time

local_time = time.ctime()
print(local_time)

Delay code execution with "Sleep"

import time

print("This is printed directly.")

time.sleep(7.1)
print("This is printed after 7.1 seconds.")

Current Date

Dim MyDate
MyDate = Date 'MyDate contains the current system date.

Debug.Print MyDate

Date additions or subtractions

DateAdd(interval, number, date) interval = interval of time you want to add. Years, Months, Days number = number of years, months, days date = date you wish to add to.

In the below example my number to add is 3 of something. Keep in mind that I can also go back 3 of something with -3 instead.

Add years

MyNewDate = DateAdd("yyyy", 3, MyDate)
Debug.Print MyNewDate

Add Months

MyNewDate = DateAdd("m", 3, MyDate)
Debug.Print MyNewDate

Add Days

MyNewDate = DateAdd("d", 3, MyDate)
Debug.Print MyNewDate

Add Hours

MyNewDate = DateAdd("h", 3, MyDate)
Debug.Print MyNewDate

Add Minutes

MyNewDate = DateAdd("n", 3, MyDate)
Debug.Print MyNewDate

Add Seconds

MyNewDate = DateAdd("s", 3, MyDate)
Debug.Print MyNewDate

DateSerial function

DateSerial(year, month, day) Great function for configuring dates without using the system languages.

MyDate contains the date for October 24, 1929. Known for the great stock market crash.

MyDate = DateSerial(1929, 10, 24)
Debug.Print MyDate 'returns the date in dateformat.

DateValue function

Another option to set a date is to convert it to dateformat from text with the use of DateValue function. I will show it below, however I tend to use DateSerial function mostly as I find it more clear.

Dim MyDate
MyDate = DateValue("October 24, 1969")    
Debug.Print MyDate 'Returns my date in dateformat.

DateDiff function

DateDiff(interval, date1, date2) interval = same as before (i.e "yyyy", "m", "d", "h", "n", "s)

MyDate = DateSerial(1929, 10, 24)
MyNewDate = DateSerial(1971, 10, 24)
'diff in years

MyDiff = DateDiff("yyyy", MyDate, MyNewDate)
Debug.Print MyDiff 'returns 42, which is my difference in years.

Take apart and put together a date again

'Now this exercise is good to get a sense of how to work with dates, and modify them. 
'In order to modify something it is sometimes easier to take the date apart, modify that or several parts (years, months, days), and then put it together again. 


MyDate = DateSerial(1929, 10, 24) 'our date

MyYear = Year(MyDate) '1929
MyMonth = Month(MyDate) '10, or october.
MyDay = Day(MyDate) '24th

'Now let's say I only know the pieces or needed to modify a year, month or day.
'Let's just add it again with the dateserial function.

MyCompiledDate = DateSerial(MyYear, MyMonth, MyDay)
Debug.Print MyCompiledDate 'returns my date as a dateformat again.

Custom Dateformats

'FormatDateTime(Date, [ NamedFormat ])

MsgBox (FormatDateTime(MyDate, vbGeneralDate)) 'general
MsgBox (FormatDateTime(MyDate, vbLongDate)) 'writes in text
MsgBox (FormatDateTime(MyDate, vbLongTime)) 'returns time
MsgBox (FormatDateTime(MyDate, vbShortDate)) 'probably the same as generaldate
MsgBox (FormatDateTime(MyDate, vbShortTime)) 'returns time

...Or we can go completely custom formatting with the Format function that can format most things.

'I can for example first set my date to format by using the Now() function which will return the current date.321

MyDate = Now() 'returns current date

'yy = is the year in 2 digits.
'mmm = is the month in short text. 'Oct'
'd = is the single digit day excluding 0 in front if possible.

'For example: 
Debug.Print Format(MyDate, "yy-mmm-d")

Learn more about Python here for all my posts: https://www.pls-fix-thx.com/python

If you have found this article or website helpful. Please show your support by visiting the shop below.




59 views0 comments