# how do i calculate the number of days until a certain date

T

#### Terrance1990

I used the Datediff function to calculate days until a birthday, but after
that date passed it would come up with a negative value, until that date was
more than six months past. Heres what i used:

DateDiff("d",Date(),[Birthday Date])

I then used the following:
Days Until Birthday: IIf(DateDiff("d",Date(),[Birthday
Date])<0,-DateDiff("d",Date(),[Birthday
Date])+183,DateDiff("d",Date(),[Birthday Date]))

This calculates the upcoming six months correctly, but the 183 value, or
half a year, makes birthdays that have passed in the last six months
incorrect.

I was wondering how to make the values all correct without such a long
formula, or at least make all the values correct

J

#### John W. Vinson

I used the Datediff function to calculate days until a birthday, but after
that date passed it would come up with a negative value, until that date was
more than six months past. Heres what i used:

DateDiff("d",Date(),[Birthday Date])

I then used the following:
Days Until Birthday: IIf(DateDiff("d",Date(),[Birthday
Date])<0,-DateDiff("d",Date(),[Birthday
Date])+183,DateDiff("d",Date(),[Birthday Date]))

This calculates the upcoming six months correctly, but the 183 value, or
half a year, makes birthdays that have passed in the last six months
incorrect.

I was wondering how to make the values all correct without such a long
formula, or at least make all the values correct

So if your birthday is June 15, you want to return 1 on June 14, 0 on June 15,
and 365 on June 16?

IIF(Date() > DateSerial(Year(Date()), Month([Birthday Date]), Day([Birthday
Date])),
DateDiff("d", Date(), DateSerial(Year([Birthday Date]) + 1, Month([Birthday
Date]), Day([Birthday Date])),
DateDiff("d", Date(), DateSerial(Year([Birthday Date]), Month([Birthday
Date]), Day([Birthday Date]))

Check the parentheses, I did this hastily.