Tabrez Blog

In this article, I'll show you how to work with date and time formulas.

Adding Dates:

Create a List name: PurchaseOrders:

following columns:

cancelDate (=[orderDate]+30) 

 

change cancelDate formula:

=DATE(YEAR([orderDate]),MONTH([orderDate])+[monthsToCancel],DAY([orderDate]))

 

 

 

see cancelDate changed as monthsToCancel have some values in it.

 

Let's see a new example:

Create a new list name Contract 

Edit the Title column to ContractNumber and add following columns in it:

ContractRenewal- This should be less than one month of contract expiration date.

=DATE(YEAR(ContractStartDate)+5,MONTH(ContractStartDate)-1,DAY(ContractStartDate))   or  

=[ContractExpiration]-30

ContractExpiration- This is based on # of Contract Year, after this the month and day must be same

=DATE(YEAR(ContractStartDate)+ContractYears,MONTH(ContractStartDate),DAY(ContractStartDate))

 

Now create a new column named: ContractMonths

Now again change formula for ContractExpiration

=DATE(YEAR(ContractStartDate)+ContractYears,MONTH(ContractStartDate)+[ContractMonths]
,DAY(ContractStartDate))

 

So you see very simple things makes very important changes to your list.

So calculated columns are very useful when you are creating Asset Management Type projects.

 

 

Calculating the difference between Dates and Times:

There are several formulas are available for calculating the difference between Dates and Times:

• DATEDIF
• TEXT
• INT
• HOUR
• MINUTE
• SECOND

DATEDIF:

DATEDIF is a function that gets you the difference between two Date and Time columns. And the return date type here, as in all these functions, is going to be a number. To specify whether you want the difference in days, months, or years, you tell DATEDIF what you want back by passing a d, an m, or a y in quotation marks.  

When wanting the difference in days or months sometimes you do not what to consider the years. In those cases you pass the y in front of the m or d, as shown in below image. 

TEXT: 

TEXT allows you to extract the hours, hours and minutes, or hours, minutes, and seconds between two date and time columns.

where Hours must not exceed 24 and Minutes and Seconds must not exceed 60

INT:

You can extract INT to extract the difference in hours, minutes or seconds between times.

To get hours you - the values and * that the total by 24, which of course, the number of total hours in a day.

To get minutes you'd use 1440, which of course is the total number of minutes per day.

And to get seconds you'd use 86400, which of course is the total number of seconds in a day.

You can also use the HOUR, MINUTE, SECOND functions to get those values respectively.

when you use these Hours should not exceed 24 and Minutes and Seconds should not exceed 60.

 

Let's see all these in action by creating some Demos.

Demo:

For demo, I'll use two new lists named: Orders and TimeSheet

Column (click to edit)	Type	Required
OrderNumber	Single line of text	Yes
orderDate	Date and Time
shipDate	Date and Time
daysToCompleteOrder	Calculated (calculation based on other columns)	=DATEDIF(orderDate,shipDate,"d")
Modified	Date and Time

=DATEDIF([orderDate],[shipDate],"d")    --> returns number of days took to ship

 

Column (click to edit)	Type	Required
EmpName	Single line of text
checkIn	Date and Time
checkOut	Date and Time
hoursWorked	Calculated (calculation based on other columns)	=TEXT(checkOut-checkIn,"h")
Modified	Date and Time

=TEXT(checkOut-checkIn,"h")   --> returns number of hours you worked

Let's add new Column:

HourFunctionVersion

Calculated (calculation based on other columns)

=HOUR(checkOut-checkIn) --> it also returns number of hours you worked

So HOUR and TEXT do the same job for us but the syntax for using these are different.

 

 

The Conditional difference between Times:

 

Now I am going make condition for overtime and regular for this I'll use following formula: IF(hoursWorked>8,"Overtime","Regular")

Now add two more columns to your TimeSheet list named: "ConvertIF - Overtime" and "Overtime"

and add two more columns forCheckIn and forCheckOut

ConvertIF - Overtime	Calculated (calculation based on other columns)	=IF((forCheckOut-forCheckIn>8),"Overtime","Regular")
Overtime	Calculated (calculation based on other columns)	=IF(hoursWorked>8,"Overtime","Regular")
forCheckIn forCheckOut	Calculated (calculation based on other columns)	=(checkIn-INT(checkIn))*24 =(checkOut-INT(checkOut))*24

 

 

Converting Dates:

If we want the full day of the week we pass in four d's.

If we want to use the abbreviated version, I'd pass in three d's.

Demo:

Create new column named:dayOFWeek

dayOfWeek  (calculated column) Formula:  =TEXT(WEEKDAY(checkIn),"dddd")

 

I think you enjoyed this article based on Date and Time formulas(used in Calculated Columns).