'Year' Wildcard?

PNEfinney

New member
Local time
Today, 02:01
Joined
Oct 3, 2019
Messages
19
Hi all, I need to classify time elapsed from a date and give it a classification so it can then lookup into an adjoined table and return a specified value.

At its basic it will look at a date, for e.g.: 01/01/2018 and subtract that from 01/01/2021 and it will return the class '03 Years', which is simple enough. However, i want it to only count full years, so if the first date is 01/04/2018 it will return '02 Years'.

There's a couple of ways i can think to do this, but the strongest i think is:

Method 1:

The Query looks at 01/01/2018 classifies it as 'Full', then looks at the year and subtracts that from 2021, returns '03 Years'

The Query looks at 01/04 /2018 and classifies this as 'Partial' then looks at the year and subtracts that from 2021, but -1 (minus 1), returns '02 Years'

To do this, I need to get it to ignore the year in the first instance. then bring it back in in the second.

Any ideas?
 
"partial" or full:

iif(datepart("y", #01/04/2018#) = 1, "full", "partial")

use Expression, to get the "years"

datediff("yyyy",#01/04/2018#, #01/01/2021#) + CInt(datepart("y",#01/04/2018#)>datepart("y",#01/01/2021#))
 
Last edited:
think you are overthinking it - use the datediff function as suggested by arnel.

Also are you aware that dates are stored as numbers - the bit before the decimal point is the number of days since 31/12/1899. The bit after the decimal point is the time expressed as number of seconds so far today divided by 86400 - the number of seconds in a day. So now is

?now() & " - " & cdbl(now())
26/08/2020 10:58:59 - 44069.4576273148

what you see is just a special format of that number
?format(44123,"dd/mm/yyyy")
19/10/2020
 
If you want only "full" years, the DateDiff() should be for days. Then divide the result by 365 using the \ operator rather than /.
Code:
print 600 / 365
1.64383561643836
print 600 \ 365
1

I wish Bing would bring up actual MS documentation first when you search. I had to look through several incorrect "tutorials" before I got to the one I wanted to post for you that includes ALL valid operators.

 

Users who are viewing this thread

Back
Top Bottom