Aug 2023
12:03pm, 18 Aug 2023
5,321 posts
|
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
|
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
|
Sep 2023
9:22am, 21 Sep 2023
7,726 posts
|
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
|
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
|
Sep 2023
9:36am, 21 Sep 2023
42,713 posts
|
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
|
Fields
Thanks, I’ll give that a whirl
|
Sep 2023
4:43pm, 22 Sep 2023
9,942 posts
|
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
|
GlennR
I didn’t get where I am today by trusting users.
|
Sep 2023
5:32pm, 22 Sep 2023
7,730 posts
|
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 ...
|
Sep 2023
6:30pm, 22 Sep 2023
42,715 posts
|
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?
|