Set a variable date

[Danny]

Hi People

Quick question, I'm generating a large Macro/ VBA scribt to hopefully run a report automatically for me.

I need to set the date to the Saturday just gone, i want this date to be displayed cell CE2.

For example; today's date is the 17/12/2013, i would want it to display 14/12/2013
if the date was 26/12/213 it would show 21/12/2013

and so on and so forth.

Is there a code fragment that i could use to do this?

Thank you in advance
Danny

 

[Danny]

Currently i have a table (See attached).

With the Vlookup shown it shows next Saturdays date. I then take 7 days from that date to get last Saturdays. Is there a VBA to do this?

 

[BBD]

Hi Danny,

You dont need a VBA for it.

Use this formula =TODAY()-WEEKDAY(TODAY())

Cheers,
BD

 

[SirJB7]

Hi, Danny!

To get the last Saturday date with a formula you can do this:
=A1-WEEKDAY(A1)
and with VBA code this:
I = INT(NOW())-WEEKDAY(INT(NOW()))

Regards!

PS: Didn't see the previous comment

 

[Danny]

Thank you both for your comments, very helpful and far more efficient than my poor excuse.

I know this may sound stupid but how does TODAY()-WEEKDAY(TODAY()) generate last Saturday? I would be interested, if you have time, if you could explain this?

Thanks again
Danny

 

[SirJB7]

Hi, Danny!

No question is stupid if by making it you try to learn, what may be known and simple for someone might be absolutely incomprehensible for others.

WEEKDAY is a function that accept 2 parameters, 1st mandatory and 2nd optional.
WEEKDAY (date_serial_number [, type])
Type is to let you specify in which day does the week start, if omitted Sunday is the default.

So given a date, it'll return a value from 1 to 7, corresponding to Sunday thru Saturday.

Let us take today's date: Thursday, Dec 19th, 2013, aka 19/12/2013.
WEEKDAY(TODAY()) -equal to WEEKDAY(INT(NOW()))-, will give us a 5, since it's the 5th day of the "week" that started on Sunday (Sun, Mon, Tue, Wed, Thu).
Now then, if I subtract 5 to TODAY(), at what date I arrive? Last Saturday (-1, Wed; -2, Tue; -3, Mon; -4, Sun; -5, Sat).

Hope it helps.

Regards!

PS: Just as a tip, remember that if you enter in an empty cell "=XXX(" (unquoted) and press the "fx" button at the left of the edition bar text box, you'll be prompted with the function wizard which shows every argument, gives a brief description of the function and by the bottom left link lets you access the built-in or online help. It works for any function.
The same from the VBA editor, you can place the cursor (keyboard, so click, don't hover with mouse) on any VBA reserved word and press F1 to access the same type of help.

 

[Danny]

Sir JB7

thank you very much for taking the time to explain this. It has helped a lot!! Really appreciate this the above.

Danny

 

[SirJB7]

Hi, Danny!
Glad you solved it and understand it, which is better indeed. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!

 

[Danny]

Hi,

Is there a way of setting the dynamic date a workbook name? So when the macro creates a new work book it is correctly titled? Currently I have:

'dt = Format(DateAdd("D", -4, Now), "dd_mm_yyyy")
'ActiveWorkbook.SaveAs Filename:=wbNam & dt

However this is static, if my report is run on the wrong day the date will be incorrect.

Thank you in advance
Danny

 

[SirJB7]

Hi, Danny!
The code is working fine as long as you want to save your file with a name including the date of 4 days before (today, 06_01_2014), so it isn't static, it's dynamic. BTW I'd change the format to "yyyy_mm_dd" so as to easily find files by date from the Windows Explorer.
Regards!

 

[NARAYANK991]

Hi Danny ,

If you always want the report to be dated the previous Saturday , you can use the following statement :

dt = Format(DateAdd("D",-Weekday(Now+1),Now+1), "dd_mm_yyyy")

instead of this :

dt = Format(DateAdd("D", -4, Now), "dd_mm_yyyy")

Narayan

 

[Danny]

Hello both,

Thank you for getting back to me.

Once again, both spot on and it works wonders. Thank you very much!!

 

[SirJB7]

Hi, Danny!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!