'Year' Wildcard? (1 Viewer)

PNEfinney

New member
Local time
Today, 13:44
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?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:44
Joined
May 7, 2009
Messages
19,169
"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:

CJ_London

Super Moderator
Staff member
Local time
Today, 13:44
Joined
Feb 19, 2013
Messages
16,553
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:44
Joined
Feb 19, 2002
Messages
42,970
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

Top Bottom