Add or subtract date/time in Excel and Powershell
Excel
Manipulate the date
Let’s say we have a field with a date, for example I take an easy date like : 1-1-2011 I put it in field A1
Now how to add/subtract this value in another field.
Depending on your language :
Enter the following formula to create a new date property field (of course another field)
=DATE(YEAR(A1);MONTH(A1);DAY(A1))
1-1-2014 | 1-1-2014 |
So now we have 2 fields with the same date. Now let’s add something to the original date.
For this example I add +5 to the Year, Month and Day.
=DATE(YEAR(A1)+5;MONTH(A1)+5;DAY(A1)+5)
This results in :
1-1-2014 | 6-6-2019 |
The same trick can be used to only edit one property of course, to subtract we use -5
=DATE(YEAR(A1)-5;MONTH(A1)-5;DAY(A1)-5)
1-1-2014 | 27-7-2008 |
Manipulate the time
As you can see it’s pretty easy the same can be done with the time property
=TIME(HOUR(A2)+5;MINUTE(A2)+5;SECOND(A2)+5)
Or
=TIME(HOUR(A2)–5;MINUTE(A2)–5;SECOND(A2)–5)
Powershell
Let’s create a date/time variable
$date = get-date
The $date variable contains a lot of properties
PS C:\> $date|select * DisplayHint : DateTime DateTime : maandag 3 november 2014 14:02:14 Date : 3-11-2014 0:00:00 Day : 3 DayOfWeek : Monday DayOfYear : 307 Hour : 14 Kind : Local Millisecond : 447 Minute : 2 Month : 11 Second : 14 Ticks : 635506201344476042 TimeOfDay : 14:02:14.4476042 Year : 2014
Now we can use the “add Methodes to manipulate the $date variable”
PS C:\> $date|gm TypeName: System.DateTime Name MemberType Definition ---- ---------- ---------- Add Method datetime Add(timespan value) AddDays Method datetime AddDays(double value) AddHours Method datetime AddHours(double value) AddMilliseconds Method datetime AddMilliseconds(double value) AddMinutes Method datetime AddMinutes(double value) AddMonths Method datetime AddMonths(int months) AddSeconds Method datetime AddSeconds(double value) AddTicks Method datetime AddTicks(long value) AddYears Method datetime AddYears(int value) etc.
As you can see there are a lot things that can be changed.
Manipulate the date
With the example of the Excel commands I just add +5 to the Year,Month,Day
$date.addyears(+5).addMonths(+5).addDays(+5) woensdag 8 april 2020 14:02:14
Or to subtract use –
$date.addyears(-5).addMonths(-5).addDays(-5) vrijdag 29 mei 2009 14:02:14
Manipulate the time
This can be done the sameway like described above
$date.addHours(+5).addMinutes(+5).addSeconds(+5) maandag 3 november 2014 19:07:19
Or to subtract use –
$date.addHours(-5).addMinutes(-5).addSeconds(-5) maandag 3 november 2014 8:57:09
Hopefully someone finds it useful.