Using CHOOSE and WEEKDAY to Display Wednesday and Saturday Dates With Time

[wtierce]

http://www.excelforum.com/excel-gene...day-dates.html

The above linked thread has the solution for what I am looking for. The only reason for this thread is to see if there is a way to incorporate time into the date the solution generates. I would like to do simple math with that date.

Thank you guys so much for the help.

EDIT: (For Clarification) Basically I would like it to either pick Wed. at 11pm or Sat. at 11pm.

[martindwilson]

what is your desired input and output?

[wtierce]

The input would be today's date and time and the output would be either the upcoming Wed. @11pm or Sat @ 11pm. My project now uses the current time and correctly picks Wed. or Sat. but just does not include time.

Thanks

[Jonmo1]

You can probalby just add the time to the end of whatever formula gives you the desired date..

=formulathatgivesdesireddate+"23:00:00"

[wtierce]

=CHOOSE(WEEKDAY(B2),3,2,1,0,2,1,0)+B2

Is the formula I have now and since it already uses a (+) the input format adding time with a simple + would net the wrong time. It moves the date displayed in the output cell past Saturday to Sunday at 9am.

Thanks

[Jonmo1]

So what's in B2 ? The current time?
Remove the B2, and put in the +"23:00:00"

OR
Put 23:00:00 in B2

[wtierce]

B2 is =now() formatted for date and time. If I remove the +b2 and put in +"23:00:00" it outputs a date in the year 2000. Without having the current date and time the formula will not be able to pick the correct upcoming date.

[Jonmo1]

OK, change the formula in B2 to

=TODAY()+"23:00:00"

[wtierce]

That will not work. That would arbitrarily change the current day to always 11pm. I need the current day and time so that I can know exactly how many days and hours till the cut off (the output). This is for production. So we have 2 promise dates a week (the output) and production for those days ends at 11pm.

Thanks

[Jonmo1]

The result of WEEKDAY(B2) will be the same regardless of what time value is added to TODAY()
So long as that time value is less than 24 hours, which "23:00:00" is.
B2 will still be the same Day (TODAY), so the Weekday result will be the same.

[wtierce]

I do not want to add time to the current day. The day and time is what it is. This needs to be dynamic as it will change all day long. For example right now I have 2 days and 13 hours till we hit our shutdown Sat night at 11pm. I need to be able to use that amount of time to compute production needs per hour, per shift, and per day till then. I know we can produce x amount per shift or hour. What I need is simply for the output cell of the original referenced cell to pick either sat 11pm or wed 11pm. Set times on the dynamic dates.

[daddylonglegs]

If B2 has a date and time then this formula will give you the next Wed at 23:00 or Sat at 23:00, whichever comes first

=MIN(INT(B2+"1:00")+7-WEEKDAY(B2+"1:00"+{0,3}))+"23:00"

format result cell as date/time

[Jonmo1]

This needs to be dynamic as it will change all day long

Only the time changes (because you have the time included in the NOW() function in B2)
The weekday function doesn't care what timevalue is present in B2, it only considers the date.
So The resulting DATE from your formula will not change regardles of what time the formula is calcuated.
The result of the formula will be on the same DAY, regardless if it's currently 12:15AM or 12NOON or 11:45PM
It will still pick the correct Wed or Saturday.

You've said that all you want is to change the resulting time to 11PM.


Change B2 to
=TODAY()+"23:00:00"
And remove the +B2 from your formula


Unless, are you saying the NOW() in B2 is also used for OTHER functions as well, and you don't want to effect them?

In that case...
To change the time resulting from this formula to 11PM
=CHOOSE(WEEKDAY(B2),3,2,1,0,2,1,0)+B2

Try
=INT(CHOOSE(WEEKDAY(B2),3,2,1,0,2,1,0)+B2)+"23:00:00"

[wtierce]

Unless, are you saying the NOW() in B2 is also used for OTHER functions as well, and you don't want to effect them?

Exactly.

=MIN(INT(B2+"1:00")+7-WEEKDAY(B2+"1:00"+{0,3}))+"23:00"

format result cell as date/time

This worked perfectly.

=INT(CHOOSE(WEEKDAY(B2),3,2,1,0,2,1,0)+B2)+"23:00:00"

This also worked perfectly.

Thank you both so much for your help.

[daddylonglegs]

The two formulas won't always give you the same results. If B2 is Wednesday 24th July 2013 at 23:45 then Jonmo's formula returns Wed 24th July 2013 23:00. Assuming you always want to get a future date/time then the result should be Saturday 27th July 2013 at 23:00. Perhaps you don't care about that for 23:00 to midnight......but you can amend Jonmo's solution like this to give the same results as mine

=INT(CHOOSE(WEEKDAY(B2+"1:00"),3,2,1,0,2,1,0)+B2+"1:00")+"23:00:00"

[martindwilson]

=INT(7-WEEKDAY(A1)+A1)+"23:00" for next sat and =INT(7-WEEKDAY(A1-4)+A1)+"23:00" for next wed