Google Sheets Formula Help

pramsay13

I'm working on a spreadsheet for our community group that will show how many years people have been members for.

I have in one column either the date or year that they joined. If I only have a year I don't mind changing them all to begin on 1st September that year.

Normally a session runs from September to March, so we have just finished our 20 / 21 season.

If someone joined in September 2020, then I want to spreadsheet to say they have been a member for 1 year now.

If I use the normal formulae for working out dates or lengths of service it will not register a year until we reach September again as that will be a year from when they started.

Any suggestions?

mksysb

How about subtracting to get the number of days, divide by 365, and then rounding up

GeordieGeorge

Use an if statement, so =if(time>1,time,1)

Where “time” is your calculation for number of years. For results below 1 it will return 1.

If you don’t care about fractional years then the post above is the way to go =roundup(years,0)

WaywardDriver

Basically you need to calculate the number of March's between Date1 (registration) and Date2 (presumably today).
So if registration date in A1, formula =YEAR(TODAY())-YEAR(A1)-IF(MONTH(TODAY())<3,1,0) 
This counts Dec-Mar as a full year but not Jan-Mar so you'll need to decide if this is what you want.. 

mksysb

=CEILING((B1-A1)/365)

use the CEILING function so it gets rounded up to the nearest year

Heedtheadvice

.....assuming that all the start dates are entered as date formats rather than textual format (numeric for the ones that are just recorded as a year should be fine!)

You could use datediff rounded up too. Oops in  google it is datedif.....

Also perhaps you need to consider which date you are using for comparison purposes?

For example do you want to know years of membership at the end of March or at the current date? I.e. how do you wish to handle someone who joins outwith your session period such as in May?

WaywardDriver

Heedtheadvice said:

Also perhaps you need to consider which date you are using for comparison purposes?

For example do you want to know years of membership at the end of March or at the current date? I.e. how do you wish to handle someone who joins outwith your session period such as in May?

Agree it's more a logical issue than "how do I do this in my spreadsheet?" The temptation is to jump straight in with formulae without sitting back and thinking through the logic of the problem. e.g. is Dec-Mar a full year? full year = someone who's paid fee upfront in Sep regardless of whether they use the facilities?    

ann_droid

Hi
Simple less than a year amount of days

Start	End				Days
27/02/2008	30/06/2008				125
01/07/2008	26/02/2009				241

 =IF(B7-A7>2,(B7-A7)+1,0)
 =IF(B8-A8>2,(B8-A8)+1,0)

Substitute TODAY for the end date (PLUS ONE?).

They total 365 days as you would expect.
#####################################

Membership, like AGE is

DOB:

01/03/1956

AGE

65 years

1 months

=DATEDIF($B$3,$I$2,"y")&" years"

=DATEDIF($B$3,$I$2,"ym")&" months "

=DATEDIF(B3,I2,"yd")& "days"

#######################################

Length of Membership
NAME	Simon SNORKEL				START			23/04/2021
DOB: JOINED	01/03/1956	AGE MEMB		65 years	1 months
Attains 65	01/03/2021		31/03/1980	65	1	1	3	1956
Start	End				Days	FROM	TO
27/02/2008	30/06/2008				125	01/07/2007	30/06/2008
01/07/2008	26/02/2009				241	01/07/2008	26/02/2009
TOTALS					366

##################