Excel Date and Time Calculations Under The Hood: For All Excel Versions

Contenuto dell'eBook:

Everything you ever want (or need) to know about date and time in Excel! Clear explanations, loaded with examples. All in one place.

Excel's date and time calculations are powerful but mysterious. You enter a date, such as June 20, 2014. Excel displays 20-Jun-14. Is that the same date you entered? Is it in the 20th century or the 21st century? Why is the century missing? Why did Excel abbreviate the month and reverse the day and the month?
That's just the beginning. Date calculations, such as calculating when an invoice or library book is due, calculating a person's age, or figuring out how many days are left to the end of the year, are fundamental to many real-world problems that go beyond simple data entry.


Ira's book starts with a 10 question test to demonstrate some of the complexities of date and time in Excel. See how you score:

1. Go to an empty cell. Enter 6/8. What does Excel think you entered? How is the value stored?
2. Go to another empty cell. Enter June 8, 2014. Make sure there’s a space between the comma and the year. Go to another empty cell. Enter June 8,2014, without the space between the comma and the year. What is the difference between how Excel stores and displays these two values?
3. A followup question. In another empty cell, enter 10:16 am making sure there is a space between the time and the am indicator. In another empty cell, enter 10:16am (no space between the 6 and the a). Explain the difference.
4. Display the current time and date with the text It is now (current time) (AM/PM) on (current date). Make sure that if you press the F9 key to recalculate, the current time and date are updated.
5. How many days are there between today and the end of the year?
6. You work in a busy doctor’s office. Each appointment for a patient is scheduled for 20 minutes. Using as few keystrokes as possible, create a column of time values, starting at 8:30 am, ending at 5:30 pm, starting as shown.
7. How do you display a date, with the month name and day name in the Yoruba language? June 20, 2014 should display as Eti, Osu kefa 20, 2014. How would you change the month name and day name so it displays in Hindi, no matter what the date?
8. You are a payroll clerk, logging start and end times for the night shift in Excel. In cell A1, you enter 8:30 pm as the start time. In cell B1 you enter 6:00 am as the end time, as the worker clocked out at 6:00 am the next day. In cell C1 you enter =B1-A1 intending to calculate the time worked. (A basic fact: Excel stores both dates and times as numbers. Dates are stored as whole numbers. Times are stored as the fractional part of a day. So this formula should work.) Instead of the calculation, the cell fills with pound signs ######## What’s going on? Is there any way this can be fixed?
9. How many workdays are in this year? Assume that the office is closed on Saturday and Sunday, and there are 10 holidays (fill in the holiday dates in a cell range).
10. You teach an honors class in United States history. You mention that Thomas Jefferson signed the Declaration of Independence on July 4, 1776, and that he, as well as John Adams, died on July 4, 1826, exactly the 50th anniversary of the signing. One of your students asks you to calculate the number of days between these two dates. Enter an Excel formula to calculate the answer.

Ira's book, based on his nearly 40 years of computer experience and his knowledge of Excel since its inception in 1985, answers these questions, as well as many others. After reading this book, you should feel comfortable tackling even the most complex date and time related Excel calculations, giving you a giant leg up on becoming and Excel guru.

Per maggiori informazioni: l'eBook "Excel Date and Time Calculations Under The Hood: For All Excel Versions " di Ira Krakow, edito da The Krakow Press, è in vendita in formato Kindle su Amazon.it al prezzo di Euro 3,02.