Date Math

[Rustywinger]

Can one of you enlightened brains help this poor guy figure out date math, such as a week that spans month ends/beginnings? Say I have a date 2022-05-05 and want to subtract a week, how would I account for the difference in month end days (28,30,31)?

 

Is there an elegant solution or do I have to add more arguments such as listing the months that have <X days and subtract from 31 as necessary?

 

Thanks!!!

 

Gord

[rberq]

If there is an elegant solution to do it within a macro, I have not found it.  It's a pain.  Somebody has probably written date-manipulation macros, and a search of these forums might find them. 

 

I think there is a way to call a VB script from a macro, though I have never tried it.  There are probably VB functions that would do all the work for you. 

 

Another way I think would work -- I am going to try this later because dates are such a pain:

1) Manually, set up and store "permanently" an Excel spreadsheet with the first column formatted as dates. 

2) In your macro, load the spreadsheet, transfer to it, and text-type your beginning date into cell A1.

3) In cell A2, type "=A1-7" and Excel should take care of the math.  I believe the spreadsheet stores dates in some Microsoft-invented form, so you may have to use other spreadsheet functions to extract month/day/year in recognizable form.  Google is your friend for finding those functions.

4) Copy the spreadsheet result cell(s) into the clipboard, close the spreadsheet, and the macro can use the clipboard values however you want.

[Cory]

I would subtract 7 days. But that seems too simple so I must not be understanding your question. 

Variable Modify Date/Time > Subtract > Integer or value.

[rberq]

[rberq]

Sorry.  I still think in ME3.

[Cory]

LOL. One thing that I like to point out for people is that dates are essentially a decimal with formatting options. 1 = 1/1/1900. So if one can't find an extant function, one can make or treat it like a decimal to do whatever is needed. For instance in the past I've had problems showing things the way I want like total hours, colon, and minutes. So I just break it up, multiply by 24, multiply the remainder by 60 into a different variable, and output they two with a colon between them. This way I can show something like 123:45. 

[rberq]

[Cory]

Exactly.