• 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.

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.
 

lengtmp

New 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.
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

Peter Bartholomew

Well-Known Member
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")
 

Peter Bartholomew

Well-Known Member
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.
 

Peter Bartholomew

Well-Known Member
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

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.
Thanks for sharing the attachments, will let you know soon.

Regards,
Kevin J. Hodge
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
 

p45cal

Well-Known Member
try changing the
MONTH(@currentDate) > MONTH(previousDate)
part to:
EOMONTH([@Date],0)>EOMONTH(previousDate,0)
or:
EOMONTH(@currentDate,0) > EOMONTH(previousDate,0)
 

Peter Bartholomew

Well-Known Member
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

Top