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