• New Patreon Tier and Early Access Content available. If you would like to support AVForums, we now have a new Patreon Tier which gives you access to selected news, reviews and articles before they are available to the public. Read more.

Another Excel

ldoodle

Distinguished Member
Hey,

Our financial year runs from May to April. One cell is a drop down list for the month (which also starts with May and ends with April).

If the selected Month is Jan to April the year should be the current year. If the selected Month is May to Dec it should be the previous year.

This works, however, it falls down when the current month is May to Dec as it should then use the current year, not the previous year.

Not sure how to solve this. Basically need:

if current month is Jan-April and selected month is May-Dec use previous year else if current month is May-Dec and selected month is May-Dec use current year else if current month is Jan-April and selected month is Jan-April use current year

!!
 
Last edited:

DPinBucks

Distinguished Member
Say the month you're looking at is in Cell A1.

Use these formulae:


IF(A1<5, YEAR(NOW()-1, YEAR(NOW()). This gives the correct year. Put this in Cell A2, say

Then the result you want is given by
DATE(A2, A1, DAY(NOW()).

You could combine them:
DATE(IF(A1<5, YEAR(NOW()-1), YEAR(NOW()), A1, DAY(NOW())
 

ldoodle

Distinguished Member
No that's not right.

We're in April now, so if I specify May-Dec it should be last year. If I specify Jan-Apr it should be this year.

Then next month we'll be in May, so if I specify Jan-May it should this year and any other month should be last year.

And in September this year, Jan-Sep should be the current year, Oct-Dec last year.

Basically, upto the current month should be this year, anything else, last year.
 

ldoodle

Distinguished Member
Writing and reading it makes it a bit more sense:

=IF(MONTH(1&B3)<=MONTH(TODAY()),YEAR(TODAY()),YEAR(TODAY())-1)
 

DPinBucks

Distinguished Member
No that's not right.

We're in April now, so if I specify May-Dec it should be last year. If I specify Jan-Apr it should be this year.

Then next month we'll be in May, so if I specify Jan-May it should this year and any other month should be last year.

And in September this year, Jan-Sep should be the current year, Oct-Dec last year.

Basically, upto the current month should be this year, anything else, last year.
Yes, sorry. The '<' should be '>'
 

ldoodle

Distinguished Member
OK, here's another.

Now I have it setup, I'd like each Month as it's own sheet. So I copied the sheet 11 times. This has created 11 other data connections.

How do I change what data connection is used - they all have exactly the same query with the month in cell B3 passed as a parameter, so I don't need or want the same connection 12 times - only once using the specified parameter in each sheet.

Otherwise, there's no point in using parameters....!
 

ldoodle

Distinguished Member
And another: database starting fiscal year = 2013-2014. Let's say we're now in 2017, I need a drop down list that shows:

2013-2014
2014-2015
2015-2016
2016-2017

and when we're in 2020, it shows:

2013-2014
2014-2015
2015-2016
2016-2017
2017-2018
2018-2019
2019-2020

So basically, start at the current year, and all previous years only going back to 2013-2014.
 

The latest video from AVForums

Guardians of the Galaxy Xmas Special, Strange World, Bones and All, and Cabinet of Dr Caligari in 4K
Subscribe to our YouTube channel
Support AVForums with Patreon

Top Bottom