A few simple Excel macros I use for automatic data maintenance.
Date Macro
| Current date/time |
| =NOW() |
| 9/7/2011 17:57 |
| Date formatted as text |
| =TEXT(NOW(),”mm/dd/yyyy”) |
| 09/07/2011 |
| Future date (5 days from today) |
| =TEXT(NOW()+5,”mm/dd/yyyy”) |
| 09/12/2011 |
| A year 180 days before today |
| =TEXT(YEAR(NOW()-180), “00”) |
| 2011 |
| A month 58 days from today |
| =TEXT(MONTH(NOW()+58), “00”) |
| 11 |
| Year, formatted |
| =TEXT(YEAR(NOW()), “0000”) |
| 2011 |
| Month, formatted |
| =TEXT(MONTH(NOW()), “00”) |
| 09 |
| Day, formatted |
| =TEXT(DAY(NOW()), “00”) |
| 07 |
| Weekday of today as number (1 – Monday, 7 – Sunday) |
| =WEEKDAY(NOW(), 2) |
| 5 |
| Workday or not? |
| =IF(WEEKDAY(NOW(), 2)<=5, “Work”, “Rest”) |
| Work |
| 2 work (business) days from today |
| =IF(WEEKDAY(NOW()+3, 2)<=5, TEXT(NOW()+3,”mm/dd/yyyy”), TEXT(NOW()+3+2,”mm/dd/yyyy”)) |
| 09/12/2011 |
Date Calculations
| Date from components |
|
| Year |
2010 |
| Month |
6 |
| Day |
15 |
| =DATE(C24,C25,C26) |
|
| 06/15/2010 |
|
| Difference in days (first parameter must be always earlier) |
|
| Year |
2010 |
| Month |
6 |
| Day |
15 |
| =DATEDIF(DATE(C30,C31,C32),TODAY(), “D”) & ” Days” |
| 451 Days |
|
