Addition and subtraction of dates, times, minutes

Here we will explain about the addition/subtraction of ‘dates’ and ‘times’, something we often use in Excel.

 

How to calculate the date) Addition・Subtraction

The list below shows members’ information for a video rental shop. The expiry date for membership is 1 year, and 2 weeks prior to that date, a notice is sent to the members to encourage them to renew their membership.

In column E, we will set up a formula that will display the date of sending the notices.

① Subtract 2 weeks from expiry date.

We subtract ’14’ (2 weeks) from the expiry date.

=D4-14

We can also set up formulas like these with addition.

 

How to calculate the time) Addition・Subtraction

The table below shows the arrival time of 4 students when they rush from their house to school.

Here, we will calculate ①the time difference between sunny and rainy days, and ②the arrival time on a sunny day without the 2 minutes of waiting for railroad crossing.

① Subtract arrival time of sunny days from rainy days.

We subtract ‘Sunny’ from the bigger number, ‘Rainy’.
(※We cannot subtract time from a smaller number than the other)

=C4-D4

② Subtract waiting time for railroad crossing (2 minutes) from arrival time of sunny days.

To calculate time such as ‘add ~ hours’ or ‘subtract ~ minutes’, we express it in the following way.

1 hour → 1:00
10 minutes → 0:10

Therefore, to ‘subtract 2 minutes’, we input ‘0:02’ and surround it with ” (double quotations).

=D4-“0:02”

 

Compare now and then) Check schedule from today

Below is a company’s task schedule. When the task is done, ‘Completed’ will be manually typed in column D.

We will compare ‘Today’s date’ with ‘Deadline’ and set up a formula in column E to display the following:

If completed, ‘As planned’
If today’s date is past deadline, ‘Late’
If deadline is ahead, leave blank

① Change the display in ‘Timing’ according to today’s date and deadline.

(1) If cell E2 ‘Today’s date’ is bigger (more current) than cell C5 ‘Deadline’, follow condition (2) and if not, leave blank.

=IF(E2>C5, (2) ,””)

(2) If cell D5 is ‘Completed’, display ‘As planned’ and if not, ‘Late’.

IF(D5<>”Completed”,”Late”,”As planned”)

(3) Combine formulas (1) and (2).

=IF(E2>C5,IF(D5<>”Completed”,”Late”,”As planned”),””)

 

Display days until the end of the month automatically

To count the days until the end of month, first we must calculate the last day of the month.

=EOMONTH(start_date, months)

[start_date] = Select cell with date.
[months] = Identify month counting from [start_date]. (※If same month, input ‘0’)

Definition of function A function that displays the last day of the month counting the [months] from the [start_date].
EXCEL version Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

Example of use) EOMONTH function

Using the same task schedule, we will calculate the last day of month in cell C14 and days until last day of month in cell E14.

① Calculate payment date (last day of the month).

Select cell E2 for the [start_date] and input ‘0’ implying this month for the EOMONTH function.

=EOMONTH(E2,0)

② Calculate the days until payment with subtraction.

Subtract cell E2 ‘Today’s date’ from cell C14 ‘Payment date’.

=C14-E2

 

Display dates automatically/NOW・TODAY, YEAR・MONTH・DAY・DATE function

To manually type and update current date for every task is time consuming.
There is a function that updates the date and time automatically every time we open Excel. Here we will explain that function along with other functions that are relevant in calculating date and time.

=NOW()

※No need to identify an argument for this function.

Definition of function A function to display the current date and time.
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=TODAY()

※No need to identify an argument for this function.

Definition of function A function to display current date.
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=YEAR(serial_number)

[serial_number] = Select cell with date. (※Must be in a ‘date’ cell format)

Definition of function A function that converts the [serial_number] to a year.
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=MONTH(serial_number)

[serial_number] = Select cell with date. (※Must be in a ‘date’ cell format)

Definition of function A function that converts the [serial_number] to a month.
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=DAY(serial_number)

[serial_number] = Select cell with date. (※Must be in a ‘date’ cell format)

Definition of function A function that converts the [serial_number] to a day.
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=DATE(year, month, day)

[year] = Select cell with year.
[month] = Select cell with month.
[day] = Select cell with day.

Definition of function A function that combines [year], [month] and [day] and converts it to a ‘date’ cell format.
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

What is a serial number?
A serial number is used in Excel to express time.
Usually it is a value that “has a ‘date’ cell format”.

 Example of use) Functions relevant to date/time

① Display current date automatically.

There is no need to input an argument. The date is updated/renewed every time Excel is opened.

=TODAY()

② Display current date and time automatically.

There is no need to input an argument. The date is updated/renewed every time Excel is opened.

=NOW()

③ Display only the ‘year’ from cell with date.

Select cell with date, E4.

=YEAR(E4)

④ Display only the ‘month’ from cell with date.

Select cell with date, E4.

=MONTH(E4)

⑤ Display only the ‘day’ from cell with date.

Select cell with date, E4.

=DAY(E4)

⑥ Combine ‘year’ ‘month’ and ‘day’ and convert result to a ‘date’ cell format.

Select cell D8 for ‘year’, cell F8 for ‘month’, and cell H8 for ‘day’.

=DATE(D8,F8,H8)

 

Shortcut to display today’s date/time

We can use the ‘shortcut key’ to simply display the current date and time without using functions.
→ For more information on shortcuts, click here

 

Calculate time exceeding 24 hours

Sometimes we might want to calculate time exceeding 24 hours, like calculating work hours for the month. To do this, there are points we must be aware of.

The table below shows the work hours in a month of an employee.

In cell C12, we set up a formula using the SUM function to calculate the total of cell range [C4:C11].

=SUM(C4:C11)

However, the results shows that the employee only worked for ’14:00′, meaning 14 hours, which is incorrect. This is in fact because Excel’s cell format is in a setting where it does not display time exceeding 24 hours. Therefore, we must change its setting.

 

Change the setting for cell format) Display time exceeding 24 hours

Below are the steps to change the setting for display.

 

(1) Right click on cell needed to be corrected, and choose ‘Format cells’ from tab.

(2) From the dialog box ‘Format cells’, follow the steps below.

① From the [Category], choose ‘Custom’.
② Input or choose ‘[h]:mm’ from [Custom] in category.
③ Click the OK button.

 

(3) Results after changing the setting

 

Convert date to text and vice versa

Lastly, there might be times when we would like to “display this date as a text”, or “display this text as a date”.

Using the ‘format cells’ tab is one way, but there is a function that can do this conversion as well.

 

Convert date to text, text to date) TEXT・DATEVALUE function

=TEXT(value, format_text)

[value] = Select cell with value (‘date’ cell format).
[format_text] = Input display format such as “yy/mm/dd” or “#,##0” as in the ‘Category’ in ‘Format cells’.

Definition of function A function that converts value to a specified text.
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=DATEVALUE(date_text)

[date_text] = Select cell with text (‘date’ cell format).

Definition of function A function that converts text to a serial number (‘date’ cell format).
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

① Display cell with date as a text.

For [value] in the TEXT function, select cell with date (‘date’ cell format), B4. For [format_text], input ‘yyyy.m.d’ and surround it with ” (double quotations).

=TEXT(B4,”yyyy.m.d”)

※The results will be categorized as a ‘text’ by Excel.

② Display cell with text (date) as a serial number (‘date’ cell format).

(1) For [value] in the TEXT function, select cell with text (date), D4. For [format_text], input ‘0000!/00!/00’ and surround it with ” (double quotations). First we change the display of this value using ‘/’.

TEXT(D4,”0000!/00!/00″)

(2) Input the formula in (1) to the DATEVALUE function and convert value to serial numbers (‘date’ cell format).

=DATEVALUE(TEXT(D4,”0000!/00!/00″))

※The results will be categorized as a ‘date’ by Excel.