Working with date and times in a spreadsheet.

There's quite a lot date and time in data. Knowledge of how to manipulate date and time can be very beneficial. Dates and times are made of seconds,minutes,Hours and Days. The representation in spreadsheets is not too different.

Dates in Spreadsheets.

Dates in spreadsheets are represented by serial numbers different from regular numbers. Operations like sorting,addition,subtraction on these numbers do not provide expected results. According to Microsoft, serial number 1 represents 1st January 1900 at 12:00:00 am. Increasing or decreasing these serial numbers produces different dates.The number 30010 represents 28th February, 1982. There are several functions for manipulating date in spreadsheet.

Time in Spreadsheets

Times are stored in spreadsheets as decimal numbers that goes from .0 to .99999, .0 represents 00:00:00 while .99999 is 23:59:59. As in dates, increasing and reducing these numbers will produce different times.

Manipulating Dates and Times

 

Above is the format that represents date and time in Spreadsheets. The 0 represents a digit. The representation is DD/MM/YY HH:MM:SS. YY can also be YYYY. The software checks the 1st two digits, if the number is equal to or less than 31, that becomes the day value, it then checks the next two, if the number is equal or less than 12, that's the month value. The last two or four becomes the year value. The software will recognize any subset from the above mentioned number pattern.

'12-03' typed into a cell will be interpreted by the software as 12th of March. '03/2004' will be March of 2004.

Did you know that?

Gives

Date and Time functions in Spreadsheets

  

There are a lot functions in spreadsheet that manipulate date and time. This web page from Microsoft discusses all these functions in details. A few thing to note about these functions:

1. Most functions work by splitting up components that make up the date and time in spreadsheets. For instance 'HOUR' function takes the hour function in date and time for calculation.
2. Functions can be combined or 'nested' with other function to achieve objectives whilst calculating.

 

Above is an example of nested functions to get the serial date for the last day of the current year.

Enjoy working with dates and times.

 

 

The best way to capture and annotate images on the monitor

At some point, there will be a need to capture images on monitors and annotate , for use in reports, presentation, writings etc.

The basic way of getting this done is by using the 'PrtSc' button on the keyboard and annotating with a graphic software like Paint (for windows users). The process is the same for Linux user, 'PrtSc' prints the entire window. There are so many software that capture screen across the platforms, a search on Google will show most of them.

For users of Windows Vista, 7 , 8 and 10 , there's this wonderful software called Snipping Tool, it has to be in my view the best tool for screen capture. , represented by the icon shown below.

 

Click on the icon above (which can be found through a search or the favourite icons section), displays the window below.

 

Clicking on 'New' displays snipe options , which has been explained by windows.microsoft.com as :

 

Snips once made, can be saved as :

An Image file: Snips can be saved as (PNG,GIF and JPEG) file which be annotated and used for various purposes.

A HTML file: They can be also saved as a special type of HTML file called MHTML (Mime Hypertext Markup Language) which is optimised for emails, meaning that snips can be sent in emails

The software has one big flaw, it cannot annotate, does not have any text functions. Annotation can be done with any graphic software like Paint. However there's one graphic software that does the job quite well. It's called Draw.

The Draw software is an open source graphic software, originally part of the Openoffice Suite, that came with Writer,Calc, Impress as a component in the productivity suite from Apache. A fork of the software was named Libre was made when Apache was acquired by Oracle. The Open office and Libreoffice Draw are both available for downloads.

Open office Draw download page 

Libreoffice Draw comes as part of the suite and cannot be downloaded separately, to download suite ,go to Libre Office Download page.

The reason for the choosing of 'Draw' over other graphic software is more tools to work with. It's got more connectors, symbols and font work, it's just like Microsoft Word that is optimized for graphics.

To load image , click on the 'Insert' from the horizontal menu and select image from from the drop-down menu and then choose 'File' to select the filepath to the image file , choose 'Scan' to get image from scanner, choose 'Fontwork gallery' to use click-art from the system.

Once image has been uploaded to page, annotations and symbols can be added with the tools at the bottom of the page . Completed work can be saved as PDF (Portable Document Format) but a good alternative is snipe the work directly from the monitor. This method enables for cropping to the right size to be done at the same time.

Enjoy Snipping.

How to organize data in a spreadsheet

Spreadsheet software is an interactive software for analyzing, organizing ,charting and storing data. The software works by manipulating data stored in cells that make up rows and columns. A column is a cluster of cells arranged vertically while a row is a horizontally arranged cell cluster.

Data types in a spreadsheet software

For a spreadsheet software to work effective, it has to classify inputted data into data types that it recognizes and is able to process, the spreadsheet software recognizes 4 data types namely:

Dates and Times: Date data is usually a combination of any day,month and year data, so it can be day,month ; month, year ; day, month,year combinations . Time data is a breakdown of the day data where it combines seconds,minutes,hours. The software is able to recognize days of the week, months of the year automatically. It is able convert from one type to the other e.g seconds to minutes, minutes to hours.

Texts : These are basically combination of letters,numbers and symbols represented in data. The numbers in texts can not be used in calculations as they are enclosed in literals (which means a notation that represents a fixed value in a code) e.g '2' or "2345", numbers like this are not recognized by the software as numbers.

Values: These are regular numbers ,not enclosed in literals and so can be used in calculations. They can also be derivatives of a calculation

Formulas: These are instruction to the software, usually a combination of text and number put together in a manner that the software will decipher and have the task done just as requested.

Rules on how to organize data in a Spreadsheets.

Spreadsheets work better when data is in a tabular form. A good data table should :

a) Have a header row showing column labels

b) Have rows and columns

c) Each cell in the column must have data matching column labels

d) Each cell in the row may not have the same type of data because the row cells combine to make a record.

e) There should be no gaps between data in row and column cells

f) There should be no spaces in data inputted in the cells

The diagram below shows an annotated data table where all the guidelines mentioned above are met. The first row is the header row with the column labels, column data in line with column label, no gaps in row and column data, row cells combining to make a record.

 

Data Formating

This means presenting data in a manner that the spreadsheet software can interpret. Since data is made up of text and numbers, formating applies to both.

Text formating is changing the appearance of text, by changing font, position, color, weight etc.

Number Formating is merely instructing the software on how to interpret number data. Number data can be:

a) Percentage

b) Financial

c) Scientific

d) Decimal

e)Fractions

f) Rounded

Date and Tine Formating is an extension of number formating because dates and year in computing are number data, it can be:

a) Second

b)Minute

c)Hour

d)Day in numbers not letters

e)Date

f)Month

g)Year

Number formating can be done in two ways. The first way using the graphic user interface by right-clicking on a cell and selecting the 'Format Cells' option, and selecting the format of choice from the Category. Date and Time formating can be done this way as well.

The other method is to use the formula 'TEXT' to specify what format the data should be in. The syntax for the formula is:

TEXT(Cell to be formated,Format"Format Pattern")

For example, TEXT(now(),Format('ddd'), gives what day it is in full, Monday, Tuesday whatever the day is.

Formula formating points to a different cell and formats its content while GUI (Graphic User Interface) formating can convert contents of the same cell.

The software is able to choose a format from the data inputted in a cell. e.g 12/04 typed in a cell will be displayed as 12th April. Data that has been put in the right way is easier to process into Information, for Spreadsheet newbies a good knowledge of data formating is a good place to start.

 

How to read emails from multiple accounts at once

Most people have more than one email account. For instance one account for work or business and another account for personal or private correspondences.

In most cases, these email accounts are attended to , one at a time by going through the web browser to the provider's web page e.g. Yahoo, Gmail and Hotmail accounts. People that can afford it get expensive email clients like the Outlook software that is part of Microsoft Office Suite. There are other email clients that can do the job, some of them are eM Client, Windows Live mail ( for window users with original copy of the software), claw mail. There are a few others that search on Google might reveal, but none does the job as well as Thunderbird email clients ( made by Mozilla Foundation, the same people that made the Firefox web browser

). This software has got to be the best email client for its price (which is free by the way). It does not come with all the functionality of high end email clients like Outlook Exchange ,it is not far off. It is so good that there's been need for comparison. Click on the link Thunderbird & Outlook Compared

Getting Thunderbird is quite easy, just google thunderbird email client and a link to the Mozilla Thunderbird website will show up in your search result. The page should look like the one below

 Clicking on 'free download', selects the right software to install according to the operating system of machine. If that fails, clicking on 'Systems and Languages' brings out a page showing languages and operating systems that the software comes, just like the link says. Installation should be fairly straight forward from here on out. The English languge sections will appear just as shown below.

 

After installation, the next step is adding of email accounts, which is again quite straight foward. Clicking on 'File' will shows options of setting up a 'New Email Account' or an 'Existing Email Account'. The 'New Email Account' is for setting up an email account from scratch, thunderbird provides email accounts to user. The 'Existing Email Account' is for setting up an existing email account, a gmail, yahoo account or a domain email account ( the email account account that comes with a website) for example. The account set up page looks as shown below.

 

The setup process is simply filling out the the mail account setup form, typing in name your email account and password. The software automatically verifies the mail address and checks the validity of the password and the account is set up. Shown below is what the software looks like with Gmail, Hotmail and a domain mail account added.

 

Pressing the 'N' key displays the next email. There's a prompt when moving to the next mail account. Sending emails is done by clicking on the 'Write' button, it is possible to send email from any account of the account when the 'Send Mail' page is displayed. Clicking on the 'From' section of the page displays a drop- down box showing all email accounts from which one can be selected as shown below.

 There are add-ons to import contacts from Google, for Calendar, for instant messaging from providers like Facebook and Google. Download this software and see what it can do for you.