Jump to content
Macro Express Forums

Recommended Posts

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?





Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...