HAN.xlsx [Read-Only] - Excel at Microsoft Excel!
Home
Introduction
Excel
101
So, you're new to spreadsheet huh.. Check this out!
Excel
Formula
How to write an excel formula!
New Posts
Love
Meter
Who is Your Crush's Big Time Crush?
Unprotect
Sheet
Remove password from protected worksheets and workbooks!
Dictionary
Attack
Open password-protected excel file using a dictionary attack!
Projects
Gag
Love
Meter
Who is Your Crush's Big Time Crush?
Other
Gag
Coming soon!
Social Network
Excel
Facebook
Coming soon!
Excel
Twitter
Coming soon!
Games
Formulas
Financial Modeling
Developer
Passwords
Password Recovery
Unprotect
Sheet
Remove password from protected worksheets and workbooks!
Dictionary
Attack
Open password-protected excel file using a dictionary attack!
Brute-force
Attack
Learn how to open password protected Excel File! Coming soon!
About
HANxlsx
About
Me
A little intro..
Privacy
Policy
Privacy Policy!
Disclaimer
Disclaimer!

[back to top!]

Coming up next!

Chatting and updating status using excel spreadsheet like a boss!

Our next excel project is to create a facebook-like excel spreadsheet that works like facebook. You don't say.. :D

Stay tuned, people! More cool stuff is coming!

Saturday, November 3, 2012

VALUE(text) Converts Dates Stored As Number

Have you ever used =VALUE(text) formula? VALUE converts a text string that represents a number to a number. Text can be in any of the constant number, date, or time formats recognized by Microsoft Excel. If text is not in one of these formats, VALUE returns the #VALUE! error value.

You do not generally need to use the VALUE function in a formula because Excel automatically converts text to numbers as necessary. This function is provided for compatibility with other spreadsheet programs. Surprisingly, it can also convert dates as number. 




So, here is how it works. First, enter specific date to cell A12, let’s use April 2, 1986 (why A12? This is just an example.. just follow the instruction for god’s sake..) And then, you give the formula to your date. Type in =VALUE(A12). See the following screenshot.


The formula returns a number. So now you have five-digit number 31504. But what exactly is it? It’s awesome that you have a number, but now you need to know what to do with it. What do you think this means?

Well, Excel actually sees dates as integer. Excel stores dates as sequential numbers that are called serial values. For example, in Excel for Windows, January 1, 1900 is serial number 1, and April 2, 2012 is serial number 31504 because it is 31,504 days after January 1, 1900. Excel stores times as decimal fractions because time is considered a portion of a day.

Because dates and times are values, they can be added, subtracted, and included in other calculations. You can view a date as a serial value and a time as a decimal fraction by changing the format of the cell that contains the date or time to General format or use =VALUE(text).



Some days, you wake up and don’t even know what day it is. Excel doesn’t have this problem. To have Excel return the current date, select the cell you want Excel to show the current date in and type the cell formula =TODAY(). The selected cell displays today’s date, and Excel automatically changes the format of the cell to Date.