Category: Excel

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.