Trying to extraploate dates

**EXCEL_HURTS_MY_HEAD** · 07-18-2024, 07:50 AM

I have a list of dates in F5:G14, column F is a list a start dates for working abroad, and column G is a list of end dates, return to the UK.
Dates within this table can range from the previous year, ie F5=01/11/2023, and trips can and usually do straddle months, ie G5 = 10/02/2024.

Below I have another table to calculate monthly wages due.
Months are in A21:A32.
Days worked is in B21:B32, this is what I am trying to write a formula for.
I currently have this formula below in C21 to try and extraplotate only the days worked in January but I am not getting the correct figure, I am returning "0" and not "31" as expected from the dates in F5 & G5
Another factor to be taken into account, is that there may be multiple trips within the same month, so hence why the formula has selected the entire table of dates

=SUMPRODUCT((MONTH($F$5:$F$14)<=MONTH($A21))*(MONTH($G$5:$G$14)>=MONTH($A21))*(YEAR($F$5:$F$14)<=YEAR($A21))*(YEAR($G$5:$G$14)>=YEAR($A21))*(IF((MONTH($F$5:$F$14)=MONTH($A21))*(YEAR($F$5:$F$14)=YEAR($A21)),DAY($G$5:$G$14)-DAY($F$5:$F$14)+1,IF((MONTH($G$5:$G$14)=MONTH($A21))*(YEAR($G$5:$G$14)=YEAR($A21)),DAY($G$5:$G$14),IF((MONTH($F$5:$F$14)<MONTH($A21))*(MONTH($G$5:$G$14)>MONTH($A21)),DAY(EOMONTH($A21,0)),0)))))

So the long and short of it, is that I am trying to extrapolate only the days between the start and end dates in the top table and only count the days occuring between those dates for each month, Jan-Dec in the bottom table

**AliGW** · 07-18-2024, 07:51 AM

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

**EXCEL_HURTS_MY_HEAD** · 07-18-2024, 07:59 AM

Please find below, a screenshot, I hope

**AliGW** · 07-18-2024, 08:59 AM

Thanks. Where shall I find:

1. The formula you want help with?
2. Your expected results (mocked up for at least a few rows)?
3. The cells that determine the start and end of the period?

**EXCEL_HURTS_MY_HEAD** · 07-18-2024, 09:06 AM

The cells that I am try work out a formula for are C21:C32
So all dates within F5:G15 occuring within Jan of the current year should be counted within C21 and all dates out with January should be discarded.
The same should apply for C22 for February etc
This will allow me to calculate the days worked per month so that I can then corrolate this to my monthly payslip.
As it stands just now cell c21 is returning "0" when the dates in F5:G5 include dates that span the whole of January, so I would expect "31" in C21 and "10" in C22 to reflect the days work.ed in February

**FlameRetired** · 07-18-2024, 04:17 PM

Please try this in C21 and copy down.

Formula:

Please Login or Register  to view this content.

Also in attached.

**FlameRetired** · 07-18-2024, 04:27 PM

Although this is a shorter formula and does the same thing.

Formula:

Please Login or Register  to view this content.

**EXCEL_HURTS_MY_HEAD** · 07-18-2024, 04:53 PM

Originally Posted by FlameRetired

Although this is a shorter formula and does the same thing.

Formula:

Please Login or Register  to view this content.

Thank you, this worked perfectly for January and February, however for March it isn't calculating

Sample.xlsx

**FlameRetired** · 07-18-2024, 05:33 PM

OK. Then see if this corrects that as well as any other 'glitches'.

Formula:

Please Login or Register  to view this content.

**EXCEL_HURTS_MY_HEAD** · 07-18-2024, 05:50 PM

That is working now for the whole year, however if I add another trip into the same month, it only calculates that last trip and disregards previous days within that month.

for example.

In F9:G9 I have a trip spanning Nov & Dec. So I should return 11 in C32, which I do until I add a 2nd trip in Dec. C32 then disregards the dates in F9:G9 and instead only counts the dates in F10:G10.

Sample (1).xlsx

**FlameRetired** · 07-18-2024, 07:41 PM

That is working now for the whole year, however if I add another trip into the same month, it only calculates that last trip and disregards previous days within that month.

I'm going to have to ponder this one. Are there any instances that cross over 2 (or more) calendar months?

Please upload a sample workbook more representative of your real data.

**FlameRetired** · 07-18-2024, 08:54 PM

Removed by FR.

**EXCEL_HURTS_MY_HEAD** · 07-19-2024, 02:00 AM

None of the dates are fixed in length, so multiple trips can occur in any month, but generally the dates 'straddle' months

**EXCEL_HURTS_MY_HEAD** · 07-19-2024, 02:01 AM

Post #10 has the latest example.

**AliGW** · 07-19-2024, 02:46 AM

I think there's a danger here that we might end up going in circles, so for the avoidance of doubt, please indicate which of the following is wrong, what it should be returning and why:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	C
17		01/01/2024
18		31/12/2024
19	2024		Days worked/MONTH
20
21	01/01/2024		31
22	01/02/2024		10
23	01/03/2024		7
24	01/04/2024		26
25	01/05/2024		3
26	01/06/2024		30
27	01/07/2024		13
28	01/08/2024		10
29	01/09/2024		25
30	01/10/2024		0
31	01/11/2024		25
32	01/12/2024		11

Sheet: Sheet1

**EXCEL_HURTS_MY_HEAD** · 07-19-2024, 03:26 AM

1 SCREENSHOT.jpg

Generally the formula given for C21:C32 is correct;

=INDEX(FREQUENCY(SEQUENCE(SUMPRODUCT(INDEX($F$5:$G$14,MATCH(EOMONTH(A21,0),$F$5:$F$14),),{-1,1})+1,,LOOKUP(EOMONTH(A21,0),$F$5:$F$14)),EOMONTH(A21,{-1,0})),2)

However as seen in F9:G10 there are 2 x trips occuring within December. The formula above doesn't seem be be working to count ALL days worked in the same month, as C32 is only counting the dates within F10:G10 although it was initially counting the days worked in G9. Once the trip in row 10 is added, row 9 appears to no longer be calculated and added to the December total in C32.
I am only using December as an example, but the same occurs if two trips occur within the same month.

What I need is a formula within C21:C32 to calculate ALL days worked in each particular month based on the dates in F5:G14

I hope that is clearer now.

**AliGW** · 07-19-2024, 03:30 AM

So what are you expecting for December, then? In your screenshot it says 5 and in mine 11 - which is right? If neither, what should the count be? I am trying to get to your expected result value.

I used the formula offered in post #9.

**EXCEL_HURTS_MY_HEAD** · 07-19-2024, 03:33 AM

C32 should equal 16, the 11 days worked in December in G9 and also the 5 days worked in F10:G10

**AliGW** · 07-19-2024, 03:35 AM

Thanks. Are any of the other months incorrect? If so, what VALUE should they be?

For future reference, when asking for a solution, you should always mock up your expected results manually - it prevents a lot of trial and error for your helper.

**EXCEL_HURTS_MY_HEAD** · 07-19-2024, 03:41 AM

It's my first time asking for help here, so please forgive me for any rookie errors.

Essentially what I am after is for cells C21:C32 to return days worked per month, based on the dates stipulated within F5:G14.
Where I am struggling is to count all the days worked in each particular month, as this comes with the added issue on 'filtering' out days not worked within each particular month, and possibly adding multiple days worked with dates given over two possibly three rows in F5:G14.

**AliGW** · 07-19-2024, 03:53 AM

I understand that you are new at this, but please answer the question I asked, which was, "Are any of the other months incorrect? If so, what VALUE should they be?"

Please refer to post #15 and tell me (a) if any other monthly values are incorrect and (b) if so, what the result (VALUE) should be. I am not asking for a description of what you want - I am asking you to correct the results shown in post #15. If it is just December that is wrong, that's fine.

**AliGW** · 07-19-2024, 04:12 AM

OK - here's a spillable dynamic array solution that I think works. Remove ALL formulae from C21:C32 so that the cells are empty, then in C21:

Please Login or Register  to view this content.

and ENTER. The results will spill down.

**AliGW** · 07-19-2024, 04:32 AM

If this solution is hurting your head even more (!), then please do ask for an explanation.

If it's what you need, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

**EXCEL_HURTS_MY_HEAD** · 07-19-2024, 04:36 AM

Yes that appears to be working perfectly. thank you so much for your help.

**EXCEL_HURTS_MY_HEAD** · 07-19-2024, 04:38 AM

If you have the time, I'd love an explanation of the =LET solution

**AliGW** · 07-19-2024, 05:05 AM

As soon as my grocery shopping has arrived, I'll sit down and write one. Glad to have helped.

**AliGW** · 07-19-2024, 05:52 AM

OK - here goes!

LET allows you to set parameters. In the attached, I have broken the formula down parameter-by-parameter so that you can see what each one does.

a - get values only from the start date range
b - get values only from the end date range
m - work out the number of days in each range (end_date - start_date + 1)
s - creates a numerical sequence using the maximum number of days in the results to m (102)
t - uses s to generate a list of ALL dates in the ranges

RESULT - uses the list of dates generated in t to determine how many days occurred in each month

Hope this helps.

**FlameRetired** · 07-19-2024, 10:31 AM

Originally Posted by EXCEL_HURTS_MY_HEAD

C32 should equal 16, the 11 days worked in December in G9 and also the 5 days worked in F10:G10

The attached does that with a helper column that looks "backwards" to pick up those extra days.

In helper column L

Formula:

Please Login or Register  to view this content.

Then the formula in column C adds those numbers.

Formula:

Please Login or Register  to view this content.

If you don't like the helper column idea that can be incorporated into the latter, but the formula becomes obnoxiously unwieldy.

**FlameRetired** · 07-19-2024, 10:41 AM

Where I am struggling is to count all the days worked in each particular month, as this comes with the added issue on 'filtering' out days not worked within each particular month, and possibly adding multiple days worked with dates given over two possibly three rows in F5:G14.

I was afraid that might be the case and at the moment I am stumped. Any "solutions" I would entertain make my head hurt. LOL

Please show us examples of that with expected results as Ali has requested.

**AliGW** · 07-19-2024, 02:12 PM

I solved the query in post #22 and explained the solution in post #27. I don't think the OP's expecting anything more.

**FlameRetired** · 07-19-2024, 04:39 PM

I see it now. Thanks for the lesson.

**AliGW** · 07-19-2024, 04:45 PM

Thanks for the rep comment - much appreciated.

**josephteh** · 07-19-2024, 08:09 PM

For work purpose and transparency reason, I would do a full workings.

With dates in cell M4 and across, in cell M5 and copy across:
=MAX(MIN($G5,EOMONTH(M$4,0))-MAX($F5,M$4)+1,0)

Do totals in cell M15 and across.

**AliGW** · 07-20-2024, 12:29 AM

@josephteh

Your formula returns zero because of this:

=SUMIFS($M$3:$NN$3,$M$4:$NN$4,">="&$A21,$M$4:$NN$4,"<="&EOMONTH($A21,0))

It should be this:

=SUMIFS($M$15:$NN$15,$M$4:$NN$4,">="&$A21,$M$4:$NN$4,"<="&EOMONTH($A21,0))

**josephteh** · 07-20-2024, 12:35 AM

Thanks for correcting the error, Ali!

**AliGW** · 07-20-2024, 12:51 AM

You're welcome.

**AliGW** · 07-20-2024, 12:54 AM

Actually, Joseph, the result for December is wrong - it should be 16 (see post #18). You also need to fill down the helper table!

**josephteh** · 07-20-2024, 04:29 AM

Thanks again, for spotting another error, Ali!

Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Re: Trying to extraploate dates

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] have list of dates, need how many of those dates fall between two other dates, monthly rpt

[SOLVED] Array of dates when tested shows no dates ......but there are dates

[SOLVED] Highlight Group of Dates if Dates Match List of other Dates

[SOLVED] Auto filling dates based on previously entered dates and averaging numbers if dates equal

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions