Hi ,
It looks like you're using an ad blocker.



The revenue generated from the adverts on the site is a critical part of our funding - and it's because of these ads that I can offer the site for free. But using the site for free AND blocking the ads doesn't feel like a great thing to do, which is why this box is so large and inconvenient. Some sites will completely block your access, but I'm not doing that - I'm appealing to your good nature instead. Did you know that you can allow ads for specific sites, whilst still blocking them on others?

Thanks,
Ian Williams aka Fetch
or for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

3 lurkers | 102 watchers
Aug 2023
12:03pm, 18 Aug 2023
5,321 posts
  • Quote
  • Pin
run free
Yeah Excel 365 and Excel 2021 have introduced #SPILL. Excel now does an "autofill" for you / enters a formula array when it thinks it is required.

So the #SPILL will mean you have an obstruction in the formula being copied down the list. In the column that you're writing your formula, clear the entire column (or say D2:D8 if that is where the formula is being written) of all entries and try again.
Sep 2023
8:41am, 21 Sep 2023
9,934 posts
  • Quote
  • Pin
Fields
So I want a formula to calculate a date based on a specified date range.

It needs to tell me the prior 28th of the month between two dates (for example 9th - 15th March) which would be 28th February, but if the 28th of the month falls within the date range (for example 26th March to 1st April) then the formula result needs to be 28th March, not the prior 28th of the month.

Hope that makes sense, I’m just not sure where to start with this one unfortunately
um
Sep 2023
9:22am, 21 Sep 2023
7,726 posts
  • Quote
  • Pin
um
What if the period is 28-30 March? Do you want 28-Feb or 28-Mar?
(Isn't the period irrelevant, you just want the 28th of the month < or <= to the start date?)
Sep 2023
9:24am, 21 Sep 2023
9,935 posts
  • Quote
  • Pin
Fields
In that case 28th March.

The date range is always a week, so if the 28th falls within the date range it is the 28th, otherwise it’s the prior 28th

It’s quite a hard requirement to define isn’t it
SPR
Sep 2023
9:36am, 21 Sep 2023
42,713 posts
  • Quote
  • Pin
SPR
It will be pretty easy to define if the date range is always a week as if the start date is between 22 & 31 it will be the same month, otherwise it's the month before.

Something like IF( DAY(START DATE) greater than 21, DATE (28, MONTH(START DATE), YEAR(START DATE), DATE (28, MONTH(EDATE(START DATE,-1)), YEAR(EDATE(START DATE,-1))
Sep 2023
9:42am, 21 Sep 2023
9,937 posts
  • Quote
  • Pin
Fields
Thanks, I’ll give that a whirl
Sep 2023
4:43pm, 22 Sep 2023
9,942 posts
  • Quote
  • Pin
Fields
Couldn’t get it to work. Need to consult with a colleague if it’s really necessary, perhaps we can trust the users to input the figure
Sep 2023
4:45pm, 22 Sep 2023
72,655 posts
  • Quote
  • Pin
GlennR
I didn’t get where I am today by trusting users.
um
Sep 2023
5:32pm, 22 Sep 2023
7,730 posts
  • Quote
  • Pin
um
Fields, what SPR said should work, with 2 tweaks
1) The entries for the DATE function should be YYYY,MM,DD (not dd,mm,yyyy) as he writes
2) add another IF statement to roll back a year if the date is before 21-Jan

Given the start date value in A1, this is what I used
=IF(DAY(A1)>21,DATE(YEAR(A1),MONTH(A1),28),IF(MONTH(A1)=1,DATE(YEAR(A1)-1,12,28),DATE(YEAR(A1),MONTH(A1)-1,28)))

Give it a try ...
SPR
Sep 2023
6:30pm, 22 Sep 2023
42,715 posts
  • Quote
  • Pin
SPR
I almost put a message saying the syntax may be wrong in my message as I wasn't at a computer.

The EDATE -1 bit should mean a second IF isn't needed though?

About This Thread

Maintained by John66
This is bugging me, I'm sure it should be much easier

A1 has 25/6/2010
B1 has 24/6/2013

So t...
  • Show full description...

Related Threads

  • excel
  • support
  • tech
  • work

Report This Content

You can report any content you believe to be unsafe. Please let me know why you believe this content is unsafe by choosing a category below.



Thank you for your report. The content will be assessed as soon as possible.










Back To Top

Tag A User

To tag a user, start typing their name here:
X

Free training & racing tools for runners, cyclists, swimmers & walkers.

Fetcheveryone lets you analyse your training, find races, plot routes, chat in our forum, get advice, play games - and more! Nothing is behind a paywall, and it'll stay that way thanks to our awesome community!
Get Started
Click here to join 114,522 Fetchies!
Already a Fetchie? Sign in here