Hi all,
I have a cell in I2 which has a date: 22/05/2021
How can I write a formula to return the date for the next Monday, Wednesday anD fRIDAY?
Basically I want the formula to change automatically with the date of the next Monday appearing first, after which the date for the next Wednesday and then the Friday's date., Is this possible?
Thank you
Thanks,
R.
Your date seems to be in cell M2, not I2. This will give you the date for the following Monday:
=M2+8-WEEKDAY(M2,2)
for the following Wednesday, just add 2 to the formula (i.e. +2 at the end), and for Friday add 4.
Hope this helps.
Pete
Hi Pete_UK thank you. sorry I meant M2.
OK so there's no way to have this done automatically without manually changing it?
I'm not sure what you mean - the 3 formulae will automatically adjust according to the date in M2.
Pete
Hi Pete.
This appears to require the manual input of a date each time to update the formula though..
I still don't understand. Perhaps you can put the formula:Originally Posted by rayted
=TODAY()
in M2, then the other formulae will automatically adjust to give you the next Monday, Wednesday and Friday after today's date (whatever that happens to be).
Hope this helps.
Pete
There is nothing in I2 in all sheets.
Anyway, below formula returns next Monday from date in I2:
=I2+CHOOSE(WEEKDAY(I2),1,0,6,5,4,3,2)
Next Wed:
=I2+CHOOSE(WEEKDAY(I2),1,0,6,5,4,3,2)+2
Next Fri:
=I2+CHOOSE(WEEKDAY(I2),1,0,6,5,4,3,2)+4
Quang PT
Hi bebo021999
thank you. sorry I meant M2.
OK so there's no way to have this done automatically without manually changing it? It would be ideal for this to be automatically done
Do you mean +0,+2,+4 is dynamic in single formula? It is possile, if you can point out from M2, which cells for next Mon, Wed and Fri?
Hi bebo21999 yes please so let's say cell M2 (actions tab) should be where the date is input:
I have written 21/05/2021
I now want the formula to return the date for Monday 24th May, then automatically it should show Wednesday 26th may then friday 28th may
thank you
What if I happen to list the dates in a tab and then have a formula return the relevant value.. could that work?
Maybe try
=WORKDAY.INTL(M2,1,"0101011")
Hi Bo_Ry
you have the right dates, but I want the figure to change in M2 to show the dates for each upcoming Monday, Wednesday or Friday... unsure if this is possible? so right now M2 would have the date for Monday 24th May, AFTER which it should change to wed 26th May, and so on
Maybe
=WORKDAY.INTL(TODAY(),1,"0101011")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks