• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Monthdate changes to the next month.

D

derek davis

Member
I am trying to alter, when the monthdate changes to the next month.
If & when next months date begins in "E" at the moment E contains IF(G346="","",(WORKDAY(E345,1,$U$3:$U$20))) which is working fine and without issue. I need the new monthdate to become visable in "M" i,e,current month Jul going forward to Aug
Can I ask for some expert advise please.
Thank you.
 
derek davis said:
I am trying to alter, when the monthdate changes to the next month.
If & when next months date begins in "E" at the moment E contains IF(G346="","",(WORKDAY(E345,1,$U$3:$U$20))) which is working fine and without issue. I need the new monthdate to become visable in "M" i,e,current month Jul going forward to Aug
Can I ask for some expert advise please.
Thank you.
Click to expand...

Hi buddy, as @Hany ali said, can you kindly upload the file for better understanding of us.

Regards,
Kevin J. Hodge
 
Hello Guys, this is really strange as soon as I saw the reply from Hany ali I sent up a workbook. I now notice that it has been removed. So I am confused as to why this would have occurred. But hey ho I have sent up another for you to peruse.

Thanks.
 

Attachments

  • Test102.xlsx
    83.3 KB · Views: 10
Why not reference to the values in column E? The difference between 5/4/20 and "Apr" is only a matter of using an "mmm" number format in the cell or within the TEXT function, e.g.
= TEXT(E5,"mmm")
 
True. If you want only the first occurrence of a month, you will need to test whether a given date matches its predecessor, e.g.

= IF( MONTH(currentDate) > MONTH(previousDate), currentDate, "" )

To find the previous start date, together with any associated data,
= IF(startMonth<>"", XLOOKUP( startMonth - 1, startMonth, currentDate, "", -1 ), "" )
= IF(startMonth<>"", amount - XLOOKUP( startMonth - 1, startMonth, amount, 0, -1 ), "" )

I appreciate these names and array formulas are not something you are likely to use but they may provide some ideas.
 
Hello.Peter thanks for your repy. Maybe I should have highlighted, that on my working worksheet the date in "E" contains workdays only (Mon - Fri) so there will be at least twenty entries per month. This means that data in "E" becomes an array. I am down to row 350 and growing.
Being a novice with using excel and the correct function is somewhat confusing. So I do appologuise if I mave misled you.
 
This may well come under the heading of 'he who thinks it is easy hasn't understood the problem'!
The attached uses a Table rather than arrays but takes the same approach to flagging the first day of each month so that month-on-month changes can be calculated.
 

Attachments

  • NewMonth.xlsx
    30.4 KB · Views: 10
Thank you Peter for you help and support. I have never used tables before and never gave a thought that configs could be done this way. I have made some small mods to suit my needs,

Thanks very much.
lengtmp said:
Thanks for sharing the attachments, will let you know soon.

Regards,
Kevin J. Hodge
Click to expand...
Thanks for replying to my query kevin. I am by noway an expert on excel so I always appreciate all advise and assistance.

Thank you
 
Hello Peter, you very kindly took a look into a problem that I was having and offered a solution back in July 2020 ( plz see 3rd reply up). Your solution worked extremely well until we reached the 4/Jan/21 ( next working day) this is the function you offered me: IF( previousDate="Date", currentDate, IF(MONTH(currentDate) > MONTH(previousDate), currentDate, "") )

I have tried to add "YEAR" so the function now reads IF( previousDate="Date", currentDate, IF(MONTH(YEAR(currentDate)) > MONTH(previousDate), currentDate, "") ) but this just populated the whole of Jan month instead and not the 1st working day of the month.

One other issue I came across was Jan 2021 does not appear but Feb does appear. Is this something to do with the year change?

I am not sure how to fix this and would appreciate some guidence.

Thank you.
Del
 
try changing the
MONTH(@currentDate) > MONTH(previousDate)
part to:
EOMONTH([@Date],0)>EOMONTH(previousDate,0)
or:
EOMONTH(@currentDate,0) > EOMONTH(previousDate,0)
 
Agreed. The formula was not written to take into account year ends. I am happy with @p45cal 's suggestion of comparing dates by using the final day of each month.
Other options would be to introduce the year into the formula
= (12 * (YEAR(@currentDate) > YEAR(previousDate)) + MONTH(@currentDate)) > MONTH(previousDate)
or to introduce new Names that characterise a month by its first day rather than its month number
MonthCurrentDate: = 1+EOMONTH(currentDate,-1)
MonthPreviousDate: = 1+EOMONTH(previousDate,-1)
Note: I haven't checked the consequences of the changes within the attached file.
 

Attachments

  • NewMonth (1).xlsx
    33.9 KB · Views: 6
You must log in or register to reply here.
Back
Top