VBA to subtract 6 days from a date

[akn846]

I have a spreadsheet which as the first column currently contains a date - which is the date of the Sunday of the given week.

What I am looking to do is to populate a second field with the date of the Monday of the same week.

I know I can do this using the DATE function within Excel, but as this spreadsheet is going to keep growing, I need a means of doing it for as many rows which are in the sheet.

So could someone suggest how I might go about doing this in VBA.

Many thanks

 

[xlhelp]

Could you please elaborate a little more?

Thanks.

Indu

 

[JGillespie]

don't make thing complicated for yourself. Excel stores dates as integer values, 1 representing the first day of the date system you have chosen (UK style dates 1/1/1900 or 1/1/1904), 2 represents 2/1/1900 or 2/1/1904 and so on.

What this means is that you can simply use a formula in column 2 that subtracts 6 from your original date. As long as the column is formatted to display dates then thats all you need.

Try the following formula in cell A2:

=IF(A1="","",A1-6)

the IF statement is there to suppress '-6' showing in rows where you have not yet entered an original date.

HTH Jamie Gillespie
j-gillespie@s-cheshire.ac.uk

 

[akn846]

Thanks for the input here, someone else pointed me the the direction of the VBA function dateadd - and this seems to be exactly what I am looking for.

But once again thanks for your help

Andy