167x Filetype XLSX File size 1.86 MB Source: d32ogoqmya1dw8.cloudfront.net
Sheet 1: Readme
Earth, Environmental and Geographic Sciences Excel Tutorial | |||||||
Author Dr. Craig Nichol UBC Okanagan Creative Commons 4.0 BY NC SA | Version 7 July 7, 2021 for Earth Educators Rendezvous | ||||||
Please send any comments, edits, or suggestions for content to include to: craig.nichol@ubc.ca | |||||||
Welcome | |||||||
This Excel Workbook has been created to provide tutorials on how to use spreadsheet software to conduct basic data storage, calculations and graphing. | |||||||
These tutorials are not intended to be all done at once, but to be done on an as-needed basis. | |||||||
This workbook was originally created to accompany various courses in the Earth and Environmental Sciences program at UBC Okanagan. | |||||||
The content and the division of content into the tutorials was designed to match the needs of different courses. | |||||||
This tutorial has also been shared with anyone who is interested in learning more about Excel. | |||||||
Those who have encountered this tutorial in another way - welcome. Please feel free to rework and modify this tutorial to suit your needs, or to share the tutorial with others. | |||||||
Please send an email to craig.nichol@ubc.ca indicating where you are sharing it and why so that I can improve the tutorial to suit how it is being used. | |||||||
General Instructions | |||||||
Menu options or keyboard commands are contained within <> marks. | |||||||
For example, <doubleclick> indicates that the mouse button should be double clicked. | |||||||
<Tools> <Options> would mean that on the upper menu you should use the left mouse button to click on the Tools menu, | |||||||
and then select the Options sub-menu | |||||||
Cell B23 is highlighted in green. In this spreadsheet tutorial, green or white cells are used to indicate places where you should be entering data. | |||||||
Cell B26 is highlighted in red. In this spreadsheet tutorial, red cells are cells in which you should not change the values otherwise the tutorial may not function. | |||||||
A large space will be shown to separate the text you should write. | |||||||
If this includes single quotes ' or double quotes “ these characters should be entered | |||||||
For example, if you are asked to enter the number twelve into a cell, the text will say | |||||||
Type 12 and press <Enter> will enter the number 12. | |||||||
Type '12 and press <Enter> will enter the text characters 1 and 2. | |||||||
Software and Hardware | |||||||
These tutorials have been written for Microsoft Excel on a computer running Windows 10 equipped with a mouse. | |||||||
The tutorials can be opened in OpenOffice Calc, Google Sheets, or other common spreadsheet software. | |||||||
However, some of the functions, and the screenshots will not be the same in other software. | |||||||
These tutorials are not intended to be opened in mobile operating systems versions of Excel, or on a touchscreen based phone or tablet. | |||||||
A version for OpenOffice Calc is in preparation (July 2021). | |||||||
PC vs Mac | |||||||
In some cases, some guidance has been given in the tutorials on how those using Mac versions of Excel may find the experience to be different. | |||||||
The <leftclick> and <rightclick> instructions for PC can translate as <Click> and <CTRL-click> in most cases. | |||||||
The <F2> instruction to reveal the target cells of formulas on a PC can be achieved by a <doubleclick> on either a Mac or PC. | |||||||
Please email craig.nichol@ubc.ca with suggestions for PC vs Mac instructions. | |||||||
Edits, additions and Updates | |||||||
Please email craig.nichol@ubc.ca if you have suggestions for edits or improvements. | |||||||
Please note that each tutorial has been written with active cells for calculations to be carried out. | |||||||
This choice was made in order that the tutorial is "live". | |||||||
This does mean that the instructions in each sheet are specific to certain cells. If you move content by inserting rows, or deleting rows, then the | |||||||
instructions and the active cells will not be properly matched. | |||||||
This version of the spreadsheet for the Earth Educators Rendevous uses American spelling (E.G.: COLOR rather than Canadian spelling color) . | |||||||
Instructors: | |||||||
Version 7 is the second public release of this spreadsheet. | |||||||
Please email to craig.nichol@ubc.ca if you adopt this tutorial in your course. | |||||||
Please send suggestions of how the tutorials can be best arranged to suit the types of spreadsheet skills you expect in your course. | |||||||
Licensing and Attribution | |||||||
This work by Dr. Craig Nichol is licensed under CC BY-NC-SA 4.0 | |||||||
This spreadsheet may be modifed or adapted. | |||||||
An attribution must be made to the the original spreadsheet: | |||||||
This spreadsheet may not be used for commercial purposes. | |||||||
Any derivatives must be released under the same licence. | |||||||
Author: | Dr. Craig Nichol, Earth, Environmental and Geographic Sciences, Univerity of British Columbia Okanagan. | ||||||
Please email to craig.nichol@ubc.ca if you adopt this tutorial in your course, or with any suggestions or edits. | |||||||
Acknowledgements: | |||||||
Peer Reviewers: | Stuart MacKinnon, UBC (June 2020) | ||||||
Nina Hewitt, UBC (June 2020) | |||||||
Crystal Huscroft, TRU (June 2020) |
Tutorial 1: Basic Data Entry, Data Types and Data Display | |||||||||||||||
Learning Outcomes | |||||||||||||||
1. Enter simple numerical data into cells | |||||||||||||||
2. Understand the display formats of simple numerical data | |||||||||||||||
3. Changing the display format of simple numerical data. | |||||||||||||||
4. Using scientific notation to display very large or very small numbers | |||||||||||||||
5. Change the format of scientifc notation numbers | |||||||||||||||
6. Enter text data into cells | |||||||||||||||
7 Convert text data to numerial data. | |||||||||||||||
8. Set the font and font settings, the background color, and cell outlines | |||||||||||||||
9. Basic copying and pasting, and how formats are pasted. | |||||||||||||||
1 Entering Data | |||||||||||||||
Selecting a cell, entering a number or text, and pressing <Enter> assigns data to a cell location | |||||||||||||||
Spreadsheets are a common software program used to store data, perform calculations, and present data. | |||||||||||||||
Data in a spreadsheet is stored in a cell. Each cell has a column which has a letter, starting at A and going to Z. After Z, the columns are identified with two letters (AA, AB, AC...AZ, BA, BB... ) | |||||||||||||||
Each cell can contain a variety of data in different formats. This can be numbers, dates, single characters, or longer strings of characters that make text. | |||||||||||||||
Cell B24 is highlighted in green. In this spreadsheet tutorial, green or white cells are used to indicate places where you should be entering data. | |||||||||||||||
Cell B26 is highlighted in red. In this spreadsheet tutorial, red cells are cells in which you should not change the values otherwise part of the tutorial may not work. | |||||||||||||||
Hover your mouse over cell B31 and click to highlight the cell. When selected, a cell will have a bold box around the cell, and this is now termed the active cell. | |||||||||||||||
With B31 as the active cell, type 12 and press <ENTER>. | |||||||||||||||
In this tutorial a large space will be shown to separate the text you should write. | |||||||||||||||
If this includes single quotes ' or double quotes “ these characters should be entered. Otherwise do not include quotes. | |||||||||||||||
Cell B31 now contains a numerical value of 12. It will display with no decimal point or digits, as the cell is interpreted to be an INTEGER value (a whole number). | |||||||||||||||
2 Number Formats | |||||||||||||||
The contents of a cell for mathematical purposes, and the format used to display that number are separate. | |||||||||||||||
Examine cell B41. It displays the value 12.6 | |||||||||||||||
Click on this cell to make it the active cell. | |||||||||||||||
12.6 | |||||||||||||||
Look to the top of the screen where there is the Input Line. | |||||||||||||||
The currently selected cell is shown: B41 | |||||||||||||||
The contents are shown: 12.57234 | |||||||||||||||
Spreadsheets can store numbers in a variety of formats. | |||||||||||||||
Integers are whole numbers. Floating Point numbers have fractions after the decimal point, and are stored to many decimal places accuracy | |||||||||||||||
Right now, Cell B41 only displays the number to one digit after the decimal point. | |||||||||||||||
However, if you were to do any calculations using this data, the full number 12.57234 would be used. | |||||||||||||||
3 Changing the Number Display Format | |||||||||||||||
You can choose from a range of formats to display numbers | |||||||||||||||
Make cell B57 the active cell. | |||||||||||||||
15.3 | |||||||||||||||
Option 1: On the <Home Tab>, look across the menu for the Cells area, and select the down arrrow beside <Format>. Click on <Format Cells>. | |||||||||||||||
Option 2: <rightclick> on the cell and choose <Format Cell> from the pop up menu. | |||||||||||||||
The dialog box to the right should open. | |||||||||||||||
Across the top of the dialog box are tabs: Numbers, Alignment, Font, etc | |||||||||||||||
On the <Numbers> tab, you should see <Options>, and <Decimal Places> | |||||||||||||||
Use the arrow keys to increase the number of decimal places. | |||||||||||||||
What value is actually located in cell B57? | |||||||||||||||
Type the value of this number into cell B69. | |||||||||||||||
4 Scientific Notation | |||||||||||||||
Scientific notation is used to make very large or very small numbers easier to use. | |||||||||||||||
A number in a cell is a numerical value which can be an INTEGER (no decimal places), or a FLOATING POINT number (with decimal places). | |||||||||||||||
Numbers can also be expressed using SCIENTFIC NOTATION. | |||||||||||||||
B83 and C83 contain the same floating point number with one digit after the decimal point. | |||||||||||||||
1863.4 | 1863.4 | ||||||||||||||
Choose C83 as the active cell. <Rightclick> and choose <Format Cells>. | |||||||||||||||
On the <Numbers> tab, under <Category>, choose <Scientific>. | |||||||||||||||
You will see a sample of the display shown in a <Sample> box. | |||||||||||||||
This indicates that scientific notation will be displayed as shown | |||||||||||||||
Click on <Ok>. | |||||||||||||||
The number 1863.4 has now been displayed as: | |||||||||||||||
1.86 x 103 | |||||||||||||||
This is equal to 1.86 multiplied by 10 raised to the power of 3. | |||||||||||||||
10 Raised to the power of three is equal to 10 multiplied by itself 3 times, or 10*10*10 = 1000. | |||||||||||||||
Thus 1.86E+03 expressed in scientific notation is equal to | |||||||||||||||
1.86 x 1000 = 1860 | |||||||||||||||
What happened to the other 3.4? | |||||||||||||||
The format that was selected only displays the first 3 digits of the number. The others are hidden. | |||||||||||||||
Keep in mind that the value of the actual data stored in Cell C83 did not change. | |||||||||||||||
The computer is still storing 1863.4, it is just displaying fewer digits. | |||||||||||||||
5 Changing the Displayed Digits in Scientific Notation | |||||||||||||||
You can choose to display more digits or fewer digits. | |||||||||||||||
Make cell B117 the active cell | |||||||||||||||
<Rightclick> and change the format to scientific notation. | |||||||||||||||
1863.4 | |||||||||||||||
Use the <Decimal Places> drop down box to change the number of digits. | |||||||||||||||
Can you now see the whole number? | |||||||||||||||
You can use this to change the number of digits displayed. | |||||||||||||||
6 Entering Text | |||||||||||||||
TEXT can be a written CHARACTER, or multiple characters that make up a STRING. TEXT has no numerical value for calculations | |||||||||||||||
Examine cell B137 It contains the number twelve and one half. | |||||||||||||||
Using what you have learned above, try to change the number of displayed digits. | |||||||||||||||
Did it work? | |||||||||||||||
12.5 | |||||||||||||||
The data entered into cell B137 is not a number. It is entered as TEXT. This means is not the value 12.5, but rather the typed text “12.5”. | |||||||||||||||
This means what occupies this cell is not the value 12.5, but rather the text character 1, the text character 2, the text character period and the text character 5. | |||||||||||||||
Together, there four characters make up a STRING of text that looks like 12.5 | |||||||||||||||
While cell B137 is active, look in the input line at the top of the screen. | |||||||||||||||
You may need to squint hard to see it, depending on the size of your screen, but just before the digits, you should see a single quote ' | |||||||||||||||
This is the symbol used in spreadsheets to indicate that what follows is to be treated as text, not as a numerical value. | |||||||||||||||
Information that carries obvious text characters like these instructions lines are automatically interpreted to be text, and no ' single quote is needed. | |||||||||||||||
If you wish to force something to be entered and interpreted as text then add a single quotation mark BEFORE the item to become text. | |||||||||||||||
For example, typing 12.5 and hitting enter would create the numerical value 12.5. | |||||||||||||||
Typing '12.5 and hitting enter will tell the spreadsheet that what you want to create is the text “12.5”, not the numerical value 12.5. | |||||||||||||||
7 Converting Text to Numbers | |||||||||||||||
Copying and pasting data into a spreadsheet may lead to number values becoming text. | |||||||||||||||
Copying and pasting data into a spreadsheet is often assisted by automatic formatting. | |||||||||||||||
Copied items that seem to be in the form of a table, or to have a format of rows and columns, will often paste into a spreadsheet in the right format. | |||||||||||||||
A common problem is that data copied and pasted that should be numbers is entered into the cells as character strings instead. | |||||||||||||||
When you try to perform a mathematical operation on the “number” in the cell, nothing happens or an error occurs, because it is text data, not value data. | |||||||||||||||
Examine cell B163 and B164, which both contain '12.5 or the text characters one two decimal five. | |||||||||||||||
Examine Cell F163 and F164. They contain the numerical value 12.5. | |||||||||||||||
12.5 | 12.5 | ||||||||||||||
12.5 | 12.5 | ||||||||||||||
Make Cell B164 the active cell. | |||||||||||||||
You could click your cursor into the Input Line at the top of the screen, and carefully delete the ' character before the 12.5. | |||||||||||||||
For a lot of data, this would be tiresome. | |||||||||||||||
Instead, with Cell B164 active, choose <Data> and select <Text to Columns> in the <Data Tools> area. | |||||||||||||||
On each screen, accept the default settings, and click <OK> and then <Finish>. | |||||||||||||||
Screen captures of the screens are shown below. | |||||||||||||||
This data conversion tool will recognize that you want this text to be converted to a value | |||||||||||||||
What happened to the format? | |||||||||||||||
You will have noticed that the 12.5 moved from being on the left of the cell, to the right. | |||||||||||||||
This is one hint that a lot of spreadsheets use as a default presentation style which can help you to separate text and values. | |||||||||||||||
By default, text is usually formatted to start on the left of the cell. | |||||||||||||||
By default, numerical values are usually formatted to be aligned to the right of the cell. | |||||||||||||||
This can sometimes help you spot format issue. | |||||||||||||||
For practice, try formatting F164 back to being text. How would you do that? What character needs to be entered as the start of the number 12.5? What happens to the display format? | |||||||||||||||
8 Basic Formats | |||||||||||||||
Data in cells can be formatted to help you distinguish material from other material. | |||||||||||||||
Cell B206 contains the text Title. | |||||||||||||||
Title | |||||||||||||||
Make it the active Cell. | |||||||||||||||
Pressing <CTRL> and <B> at the same time will make the text bold. | |||||||||||||||
Pressing <CTRL> and <i> at the same time will make the text italics | |||||||||||||||
Pressing <CTRL> and <U> at the same time will make the text underlined | |||||||||||||||
The same format changes can be found on the main menu bar. | |||||||||||||||
In the <Home> tab <Font> area, you can change Bold, Italics, Underline, Font color. | |||||||||||||||
The dropdown arrow next to the red A wil show you the colors you can assign for text | |||||||||||||||
The Paint Can icon will change background color. | |||||||||||||||
Cells can also have a background color, and borders. | |||||||||||||||
Try the various buttons contained in the Font and Aligment areas to determine their effect. | |||||||||||||||
Cells can be given borders. | |||||||||||||||
<Rightclick> and choose <Format Cells>. | |||||||||||||||
Click on the <Border> tab. | |||||||||||||||
On the left, you can select the line style you wish. | |||||||||||||||
On the right, you can select which line locations around the cells you want to change. | |||||||||||||||
Try experimenting with changing background, fonts, and borders. | |||||||||||||||
See if you can make cells B232 to C234 match those in cells E232 to F234. | |||||||||||||||
Parameter | Value | Parameter | Value | ||||||||||||
Length | 12.6 | Length | 12.6 | ||||||||||||
Width | 14 | Width | 14 | ||||||||||||
9 Copying, Pasting, and Cell Formats | |||||||||||||||
Copying and pasting data preserves the type of data being stored | |||||||||||||||
Value | |||||||||||||||
Parameter | 12.5 | ||||||||||||||
The example above contains two cells that contain Text - B247 and C246 | |||||||||||||||
The 12.5 is a numerical value. | |||||||||||||||
To copy a cell's contents, make that cell the active cell. | |||||||||||||||
Make Cell C247 the active cell. | |||||||||||||||
To copy an item, choose <Copy> from the <Clipboard> area of the <Home> main menu, | |||||||||||||||
Make C258 the active cell. | |||||||||||||||
Chose <Paste> from the <Clipboard> area of the main <Home > menu. | |||||||||||||||
What happened? | |||||||||||||||
You will notice that the value of 12.5 now appears in Cell C258. | |||||||||||||||
The color also changed from a green background to a white one. | |||||||||||||||
This is because the cell contains not only the data 12.5, but also information about how to display that number. | |||||||||||||||
In this example, the text is not bold, not underlined, not italics, is a certain size and font, and is displayed on a white background. | |||||||||||||||
The value, and all the associated formatting, was copied from C247 and pasted into C258. | |||||||||||||||
Return to Cell C247, and <Copy> the cell contents. | |||||||||||||||
This time, click on cell C269. | |||||||||||||||
With cell C269 highlighted, click on the arrow located under <Paste> and choose <Paste Special>. | |||||||||||||||
You will see the pop up menu to the right. | |||||||||||||||
This lists all of the characteristics of the cell C247 that you can choose | |||||||||||||||
to copy to a new cell. | |||||||||||||||
All | |||||||||||||||
Formulas | |||||||||||||||
Values | |||||||||||||||
Formats | |||||||||||||||
Comments | |||||||||||||||
etc. | |||||||||||||||
In this case, click on the small box so that only <Values> is selected | |||||||||||||||
The, click on <OK> | |||||||||||||||
This time, Cell 256 should now contain the value 12.5, but the background to the cell should remain green. | |||||||||||||||
Only the numerical data, the number 12.5, was copied, but not any formats, formulas or other features of cell C247. | |||||||||||||||
Tutorial 1: Summary | |||||||||||||||
This worksheet has provided some instruction and some practice in entering basic data into a spreadsheet. | |||||||||||||||
As these worksheets progress, the user is expected to be proficient in the skills from previous worksheets. | |||||||||||||||
For example, in this worksheets, you are given explicit instructions on how to make the text in a call bold, or underlined. | |||||||||||||||
In worksheets that come, these instructions may not be repeated, but you may simply be asked to “...make the text bold”. | |||||||||||||||
Typically, instructions will be repeated several times before they are simply assumed to be part of your existing skill set. |
Tutorial 2: Dates and Date Formats | ||||||||||
Learning outcomes | ||||||||||
1. Understand how to enter date and time data into a cell. | ||||||||||
2. Be able to set date and time data to different display formats | ||||||||||
1. Enter Data Using Date and Time Formats | ||||||||||
A cell can contain numbers, characters, strings, formulas, as well as time or date data. | ||||||||||
Tutorial one introduced numbers and text as possible data formats. | ||||||||||
In this tutorial, we will work with date and time data, which are special cases. | ||||||||||
Date | Time | |||||||||
YYYY-MM-DD | HH:MM:SS | |||||||||
2020/06/11 | 10:30:23 AM | |||||||||
The two cells above contain date (A25) and time (B25) data. | ||||||||||
The date is June 11th 2020 and the time is 10:30:23 AM. | ||||||||||
Click on each cell in turn, and examine the nature of the data displayed in the Input Line. | ||||||||||
You will see that the displayed format within the cell matches to how the date and time are displayed. | ||||||||||
Time and date data use standard shorthand notations to describe how data are entered or displayed. | ||||||||||
Year is Y, Month is M, Day is D, Hour is H, Minutes is M, Seconds is S. | ||||||||||
These can be combined using some standard conventions. | ||||||||||
YYYY | This means to display the full year including all digits | |||||||||
YY | This means to just display the last two digits of the year. 2018 would be 18. | |||||||||
MM | Will display the month of the year as a number from 1 to 12 | |||||||||
MMM | Will display the month of the year as 3 characters of text Jan Feb Mar Apr..... | |||||||||
DD | Displays the day (1 to 31) | |||||||||
HH: | Displays the hour | |||||||||
MM | Displays the minutes | |||||||||
SS | Displays the seconds | |||||||||
AM / PM | Determines the time of the day as before noon or after noon. | |||||||||
To enter DATE data, type in numbers in the format: YYYY-MM-DD such as 2020-07-27 to enter July 27th, 2020. | ||||||||||
When numbers are typed in separated by a - the numbers will be interpreted as dates. | ||||||||||
To enter TIME data, type in number in the format: HH:MM or HH:MM:SS | ||||||||||
When numbers are separated by a colon : the spreadsheet will interpret the data as times. | ||||||||||
A time is inherently ambigous as to whether is is AM or PM. | ||||||||||
You can resolve this by typing in the AM or PM. Type 2:14 PM to enter PM data. | ||||||||||
Another method is to use a 24 hours clock, so that 2:14 PM is entered as 14:14 | ||||||||||
2. Setting the display format for date data | ||||||||||
The data that is stored for date or time, and the manner in which it is displayed, are separate. | ||||||||||
Date | Date | |||||||||
YYYY-MM-DD | YYYY-MM-DD | |||||||||
2020/06/11 | 2020/06/11 | |||||||||
The data stored in cell A61 and B61 above are known by Excel to be date formated. | ||||||||||
What is displayed is date in YYYY-MM-DD format. | ||||||||||
Click on B61 and <Rightclick> and <Format Cells>. | ||||||||||
Choose <Number> and you will see that the format chosen is <Date>. | ||||||||||
You will see the various date formats listed. | ||||||||||
Try changing the format to different formats to see what happens. | ||||||||||
Month Day Year vs Day Month Year | ||||||||||
One of the most common points of confusion for entering and displaying | ||||||||||
dates are differences in convention over writing dates. | ||||||||||
11/06/2020 | What date is this? | |||||||||
The 11th of June, 2020 | ||||||||||
or | ||||||||||
November 6th, 2020 | ||||||||||
Different countries in the worlld have different conventions for writing dates. | ||||||||||
A common issue in spreadsheets is for people to not be clear on what format is used for date. | ||||||||||
The default way to enter dates in Excel is YYYY-MM-DD. | ||||||||||
This is the safest way to enter dates and to be sure the correct date is entered. | ||||||||||
However, dates on forms, field notes, or other forms of data used for data entry may vary. | ||||||||||
The most common way of writing dates across Earth is Day-Month-Year, which is what Canada uses. | ||||||||||
The United States uses Month-Day-Year. Other countries use other formats. | ||||||||||
If you click on A75 above, and look at the input line, you will see 2020-06-11 indicating it is June 11th of 2020. | ||||||||||
The input line shows the raw data being stored in a common format. | ||||||||||
The cell has been formatted to display that data in a DD-MM-YYYY format instead of the default YYYY-MM-DD format. | ||||||||||
This is like a cell displaying 13 or an integer (no decimals) in a cell, but the input line showing the true value | ||||||||||
stored in the cell of 12.856743 | ||||||||||
Try setting the cell A75 back to a YYYY-MM-DD format. | ||||||||||
If there is ever any potential for confusion between spreadsheet users, make sure to either include | ||||||||||
a key such as DD-MMM-YYYY in the data header to clearly tell users the format. | ||||||||||
Or, choose a format including the MMM form so that month is written as text, not a number. | ||||||||||
3. Working with Date Data | ||||||||||
The actual number stored for dates is a single number that counts time forwards from Jan 1, 1900. | ||||||||||
Date | Date | Date | ||||||||
YYYY-MM-DD | YYYY-MM-DD | YYYY-MM-DD | ||||||||
2020/06/11 | 43993.00 | 11/06/2020 | ||||||||
Click on cell C109 | ||||||||||
<Rightclick> and <Format Cells>. | ||||||||||
Choose <Number> and <Number> | ||||||||||
The format of Cell C109 will swap to match B109 | ||||||||||
Windows Excel spreadsheets use a convention for determining dates, which is to track | ||||||||||
the start of the day Jan 1, 1900 as date = 0 | ||||||||||
The number 43993 is the number of days since Jan 1, 1900 to reach Jun 11th, 2020. | ||||||||||
The actual data stored in the background of a date formatted cell is actually this number. | ||||||||||
Having it be a date formated cell simply allows Excel to interpret a number of 43993 as a date in 2020. | ||||||||||
Note: If you are doing this tutorial on a MAC, the you may have noticed a different number. | ||||||||||
Excel for MAC and some other spreadsheets use Jan 1, 1904 as day 1. | ||||||||||
The lesson: You must be very careful when swapping files between MAC and Windows users when dates are involved | ||||||||||
The same for changing between Excel and OpenOffice Calc. | ||||||||||
Always double check that all dates have been interpreted correctly. | ||||||||||
We will see this again in later tutorials when we look at graphs with DATE on the x-axis. | ||||||||||
4. Time Data | ||||||||||
Time data is actually stored as a number between 0 and 1, or the fraction of a day. | ||||||||||
Date | Time | Time | ||||||||
YYYY-MM-DD | HH:MM:SS | HH:MM:SS | ||||||||
2020/06/11 | 10:30:23 AM | 10:30:23 AM | ||||||||
Click on C139. <rightclick> and <Format Cells>. | ||||||||||
Choose <Time> as a format, and then try experimenting with different formats. | ||||||||||
Date | Time | Time | ||||||||
YYYY-MM-DD | HH:MM:SS | HH:MM:SS | ||||||||
2020/06/11 | 0.437766 | 10:30:23 AM | ||||||||
Next, click on C148. Change the format to <Number> and increase the digits displayed. | ||||||||||
You should see the same number as B148. | ||||||||||
Time is actually calculated across a day as a number between 0 and 1 as the fraction of a day. | ||||||||||
5. Date and Time together | ||||||||||
Days and times can be added or subtracted as numbers. | ||||||||||
Date | Time | |||||||||
YYYY-MM-DD | HH:MM:SS | |||||||||
2020/06/11 | 10:30:23 AM | 2020/Jun/11 10:30:23 AM | ||||||||
Click on cell C162. | ||||||||||
What is present in that cell is actually a formula that adds together the data | ||||||||||
in cells A162 and B162. | ||||||||||
This is adding together at "DATE" and a "TIME". One has dashes, the other colons. | ||||||||||
Remember, what is really in those two cells is | ||||||||||
43993.00 | 0.437766 | 43993.4377662037 | ||||||||
Date is number of days since Jan 1, 1900 (or sometimes Jan 1 1904). | ||||||||||
Time is fraction of a day between 0 and 1. | ||||||||||
Adding the two together has given us a number that contains | ||||||||||
both the calendar date and time. | ||||||||||
Rightclick on C162 and <Fomat Cells> to see how that is displayed. | ||||||||||
You will see that the cell format is <Custom>, as YYYY-MMM-DD HH:MM:SS AM/PM | ||||||||||
By choosing a <Custom> type, you can create your own date and time display using YMDHMS shorthands. | ||||||||||
Trying changing the date and time displayed in C170 to get it to match C162. | ||||||||||
Tutorial 2 Summary | ||||||||||
Date and Time are actually stored as days since 1900 and fractions of a day. | ||||||||||
How they are displayed and worked with in cells, and in the input line are slightly different. | ||||||||||
Always make sure to double check any date data, both on the data source, | ||||||||||
and in the spreadsheet, to make sure that you are entering and displaying the data | ||||||||||
the right way. | ||||||||||
The most common error is confusion between the Canadian day month year system and the | ||||||||||
United States month day year system of writing dates. |
no reviews yet
Please Login to review.