Flow - Calculate Date Diference

[Mdorrani]

Definition :- ticks(timestamp: string) - Returns the number of ticks (100 nanoseconds interval) since 1 Jan 1601  00:00:00 UT

Scenario :-

SharePoint list contains following columns:-

Title (Single Line of Text)

Contract Executed Date (Date)

 MS Flow :-

Get items from list

Run Select data operation to calculate number of days between Today's date and the Contract Executed Date

The expression for Days Since Contract Executed :-

div(sub(ticks(formatDateTime(utcNow(),'yyyy-MM-dd')),ticks(item()?['ContractExecutedDate'])),864000000000)

ticks(formatDateTime(utcNow(),'yyyy-MM-dd') - Number of Ticks since 1 Jan 1601 and Today's Date

ticks(item()?['ContractExecutedDate']) - Number of Ticks since 1 Jan 1601 and Contract Executed date

sub - subtracting the number of ticks

div - divide the result from 864000000000 (conversion from nanoseconds to days)

Output of Select when Flow runs :-

Another use case would be to do date comparisons using Ticks

Filter all contracts which have Contract executed date in the last week

ticks(item()?['ContractExecutedDate']) - Number of Ticks since 1 Jan 1601 and Contract Executed date

ticks(formatDateTime(adddays( utcNow(),-7),'yyyy-MM-dd')) - Number of Ticks since 1 Jan 1601 and [Today's Date - 7 ] (in the last week)

utcNow() - current date and time

adddays( utcNow(),-7) - adds (-7) days to todays date 

formatDateTime(adddays( utcNow(),-7),'yyyy-MM-dd') - format the date time to date 'yyyy-MM-dd'

Check if ticks - Contract Executed date is greater than the ticks  - since Last week

Data in Contracts List :-

Flow - Filter using ticks expression

Flow - Select the filtered operation result and get Tile of contract

Result of Flow run for Select -

Only "Contract 2" is returned as it has Contract Executed Date since last week

Thanks for reading !!!