Akbar - Function and Formula Excel 2016

Function and Formula Excel 2016

Function and Formula Excel 2016

2016

Copyright 2016 by Kanzul Ilmi.

All rights reserved. This book or any portion thereof may not be reproduced or used in any manner whatsoever without the express written permission of the publisher except for the use of brief quotations in a book review or scholarly journal.

Author: Ali Akbar

Editor: Zico Pratama Putra

Cover Designer: Zico Pratama Putra

First Printing: 2016

ISBN 978-1-329-91775-0

Kanzul Ilmi Press
Victoria Rd
London, UK

Ordering Information:
Special discounts are available on quantity purchases by corporations, associations, educators, and others. For details, contact the publisher at the above listed address.

U.S. trade bookstores and wholesalers: Please contact Kanzul Ilmi Press email zico.pratama@gmail.com.

Date & Time are very important things in Excel, Excel supports many functions related to date & time. This chapter describes many date & time-related functions in excel.

Note: My computer display date on DDMMYYYY format, so I hope you dont get confused when reading the content of this chapter.

Date function returns serial number that represents date in date-time form. This function needs year, month and day parameters to form the date.

For example, you can see pic below:

Pic1.1 Datefunction

If the cell formatted as General before the function entered, the cell will return regular date. But if the cell formatted as Number, it will return 42038 which is the serial date from date parameter inserted.

You can define Number format by clicking Home > Number.

Pic1.2Choosing format for Number

DATE function is very important when you need to process a separated-year-month-day values and you want to make single date value from that values.

See table below for date function examples:

Datevalue will return serial date value based on data entered. But the difference with the previous Date function is the data parameter inserted in text form, between quote sign ()

Pic1.3DateValue function

Excel saves date value as serial number to make date calculation easier. For example january 1st 1900 will have date serial number = and january 2008 will have date serial number , becaues its , days after january 1st 1900.

See table below for DATEVALUE function examples:

Day function will return the day-th value from a serial-date value. You can enter the serial date as string or number.

For example if you insert 42653 as an argument , this will return day value 10, because its the th day in the month .

Pic1.4 DAYreturns the day value from serial date

Days function is for calculating the distance between two dates. You can enter the two date arguments as regular date or serial date. You have to enter the end_date first , and then the start_date /initial_date.

Pic1.5Entering argument for end_dateand start_date

If you click Enter, the distance between end_date and start_date will be calculated and displayed.

Pic1.6Dinstance betweenend_dateandstart_date

See table below for Days function examples:

Days360 returns distance between two dates, end_date, and start_date with assumption that a year have days, or in other words, its assumed that

a year = months x 30 days .

Arguments needed in Days360 function are start_date, and end_date.

Pic1.7 Days360function

In Days360 function, you can have methods as argument:

Edate function returns the date before/after initial date after a month_interval. The parameter for this function are start_date for initial date, and before/after month_interval. Before month_interval will have negative value.

For example this function edate below will count 2 months after 1/10/15 (my computer using DDMMYY time format):