SOLVED

Yearfrac stopping dates in future

Copper Contributor

Want to use yearfrac instead of datedif and it works but we want to have it give a value of 0 if one of the dates is in the future.  I want to figure out how long someone has worked for us based on todays date and their start date. 

4 Replies
best response confirmed by mathetes (Silver Contributor)
Solution

@PWWRB 

Let's say you have dates in D2 and E2.

=IF(OR(D2:E2>TODAY()),0,YEARFRAC(D2,E2))

This can be filled down.

If you want to compare D2 to today's date:

=IF(D2>TODAY(),0,YEARFRAC(D2,TODAY()))

Awesome. I use today in a seperate tab so will give this a whirl tomorrow.

@Hans Vogelaar seems my colleague is happy with your solution. Thank you

@PWWRB 

Good to hear that. Thanks for the feedback.

1 best response

Accepted Solutions
best response confirmed by mathetes (Silver Contributor)
Solution

@PWWRB 

Let's say you have dates in D2 and E2.

=IF(OR(D2:E2>TODAY()),0,YEARFRAC(D2,E2))

This can be filled down.

If you want to compare D2 to today's date:

=IF(D2>TODAY(),0,YEARFRAC(D2,TODAY()))

View solution in original post