Working with Date values - Part I

This will be a three part series: The first part, to be covered today, will discuss ways to transform a specific date/time stamp value in a cell into data that you can use. The second part will be how to use those values to calculate differences in days or months, or any level of detail you want. The third value will explain how to change these into specific formats so you can display the data you want after you've calculated what you need.

Functions of the day:
Second
Minute
Hour
Day
Month
Year

Notice a pattern?

Two others we'll use
Date
Time

If you've ever tried to add or subtract certain amounts of time, it can get quite frustrating if you don't know how to use these formulas.

Lets look at my sample Spreadsheet:


You can see in Cell C2 I have a time, which just happens to be the time I created the time in that cell! (Nifty Trick - you can use ctrl + ; to input the date any cell, and ctrl + shift + ; to input the time. I then just changed the formatting to show you all the data)

the problem with a date/time value, is you can't just simply add or subtract from it, and have it make much sense. Instead, you need to know how to convert the values from date/time to numbers, and back.

Now lets say you wanted to turn these back into a date/time. This is where the date, and time, formulas come in.
=Date (Year,Month,Day)
=Time(Hour,Minute,Second)

so if we wanted to put my date back together, it'd be =DATE(C9,C8,C7)


Similarly, if you wanted to put the time back together, it'd be =TIME(C6,C5,C4)


We'll look at how to use these to our advantage more in the next post...