SOLVED

# Yearfrac stopping dates in future

Copper Contributor

# Yearfrac stopping dates in future

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

# Re: Yearfrac stopping dates in future

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

# Re: Yearfrac stopping dates in future

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

# Re: Yearfrac stopping dates in future

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

# Re: Yearfrac stopping dates in future

Good to hear that. Thanks for the feedback.

1 best response

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

# Re: Yearfrac stopping dates in future

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