Excel and or VBA Date and time. 3

[WaterSprite]

We have a chemical tank that is filled once per week on Wednesday afternoon.

I need either an Excel formula or VBA code that will put a Date/Timestamp of 12:00 noon on Wednesday into a cell, where it will stay for a week until replaced by noon Wednesday of the following week.

For example, I need yesterday, Wednesday, 06-Sept-06 12:00 to stay in a cell until next week when the 06 would change to 13, then reading 13-Sept-06 12:00 and so on. If this were a daily thing, no problem. A couple of Today() statements and I am done, but the once a week thing has me baffled.

I figured out one way to get what I need, but it took 26 cells on a spreadsheet to do so, I thought that was not to effecient.

And one of the MVP's brought this up a couple of weeks ago, but it is true. I really struggle with dates, times, and formatting in VBA.

Thanks for the help.

 

[PHV]

A starting point (VBA formula)
Date + 4 - Weekday(Date) + 7 * (Weekday(Date) < 4)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AnotherHiggins]

On what day of the week will it 'roll over' to the following Wednesday?

Also, just for future reference - this doesn't require VBA. The question really belongs in forum68, the Microsoft: Office Forum.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

I'm so impatient ;p

Assuming you want it to change on every Wed, then this will work:

=Int((A1-4)/7)*7+4

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Hi,

For any date within the week...

DteTime = INT((Date)/7)*7+4.5

Skip,

_[red][/red]