I have a sheet that I want the date to automatically change to the next date once the first date has passed. For instance; A2 has =IF($I$4>=TODAY(),$I$4,$P$4) and shows Thursday, December 03, 2020. What I need now is for A2 date to change to Thursday, December 10, 2020 when the 3rd has passed. So on once the 10th passes the date will go to Thursday, December 17, 2020.
Welcome to the forum.
I assume that what you want is for the date to change to the next Thursday whenever Today() gets to a Friday.
To do that, put this in A2:
Formula:
Please Login or Register to view this content.
Right now, that will give you Thursday 26th November 2020, as that's the next Thursday after today (Wed 25th Nov). But if you check it in a couple of days instead, you'll get Thursday 3rd December 2020, then 10th December the following Friday and so on.
I hope that's what you're wanting. If I've misunderstood, please just say.
Regards,
Aardigspook
I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
You don't need to give me rep if I helped, but a thank-you is nice.
Another option:
Formula:Please Login or Register to view this content.
Good luck!
Thank you very much. Honestly, I'm going to stick both of these suggestions in and give a month to see which one will work better. Basically, every month I do a save as to create the next months attendance record so I'm just automating as much as I can.
Another variation,
Formula:Please Login or Register to view this content.
Alternative way
In case jumping to nearest Thusday in future, try
=WORKDAY.INTL(TODAY(),1,"1110111")
Upgragde sugestion: In G4, in stead of using a long and complicated formula to get the fist date of given year and month, try again:
=DATEVALUE(1&E4&E1)
Quang PT
OP seems to want 2020-12-03 to appear when TODAY() is 2020-12-03 but move to 2020-12-10 when TODAY() becomes 2020-12-04 to 2020-12-10. MaybeOriginally Posted by bebo021999
=WORKDAY.INTL(TODAY()-1,1,"1110111")
Definitely, if that was the case.
But I am still waiting him back serving more delicious food.
Cheer!
I haven’t had the chance to try bebo’s suggestion yet but so far all other suggestions work great, just different ways of going about the same result. I will try bebo’s formula sometime today. I could mark this as solved but I do want to try the other formula first. As to what I was needing, yes, my goal is to have the date in a specific cell show the date for a specific day. I have certain programs I run on specific days. For instance, RESB is ran on Fridays so I want today’s date to automatically fill the date in a specific cell. After today ends, the date automatically changes to the next Friday. lol I know it’s lazy but I run different programs everyday but sometimes I have forgotten to change the date. Thank you all for the awesome help
Last edited by RSkeen; 11-27-2020 at 01:57 AM.
Just to help you to control if Thusday change to other weekday
=WORKDAY.INTL(TODAY()-1,1,"1110111")
with "1110111" , "1" indicates day off (Mon to Sun from the left), "0" indicates working day (Thusday position)
If friday to be working day, it should be "1111011"
Ohh, that sounds like yet another fantastic formula! I’ll be trying it a little later on today. I’ll report back but I expect that will be another formula for my already growing and awesome arsenal.
I've finally been able to input all of these suggestions and as of today, they all appear to be doing what I need. As the week moves on I should be able to see if the formulas react the way I am hoping. I say this because I actually have a sheet for each program that runs on different days. So each Monday I run a program, each Tuesday I run a different program, and so on. This mean that each sheet has a different date for that particular day. Ugh, I am probably making this waaayyy more confusing than it is. Suffice it to say, right now each of these formulas appear to work perfectly. The thing is, I have a folder set up just for excel formulas and all of these will go into one workbook so regardless of a situation, these will all come in handy and I can't even start to thank you all enough. You are all pure geniuses. Thank you!
You're welcome. Glad to help.
Thanks for the feedback and for the reputation added.
Have a blessed day.
I just wanted to give an update on these formula suggestions. THEY ALL WORK!! They each do exactly what I needed them to do. I will say though I am currently using bebo's =WORKDAY.INTL(TODAY()-1,1,"1110111") but I can't really say why. There is nothing complicated with any of these formulas and I will be using them all across my many other workbooks...just for kicks and giggles. Once again, thank you all.
Glad we could help and thanks for the rep, and for marking the thread as Solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks