Saturday, February 18, 2012

The basics of dates in Excel


To start with, any date can be entered in a cell, using a slash or a hyphen to separate the parts of a date, and Excel normally recognises it as a date. If it does not, you will be frustrated to find a number appearing instead and any amount of retyping does not help. To resolve this, the cell or cells need to be formatted as dates. This is very simply done, right click on the cell, select “Format cells...” , in the number tab which would have been selected automatically, scroll down to “Date” and select the style you prefer. And do make sure that you have picked your location correctly. Since I am based in India, I pick “English (India)”. This gives the date first followed by the month. A location change to “English (United States)” gives the month first followed by the date.
To use as data, Excel converts all dates into numbers. The date 1st January 1900 is the base date with the serial number “1”. And say 1st January 2012 (when someone has wished me Happy New Year) has the serial number “40909”. To find out what the number of any date is, just type the date in a cell, and then change its format to number.

To find out the difference between dates, the simple minus sign is used. The two dates may be entered in two cells say in B6 and B7 and wherever the difference is to appear, the formula is used. Here it would be “=B6-B7”. Let us say B6 is 17th February 2012 and B7 is 5th February 2012. The result displayed is “12”.

The autofill function works equally well with dates. Thus selecting B6 and B7, and dragging down, gives a series of dates with a gap of 12 days. For the basics of autofill, click here for an earlier post: http://computertipsforbeginners.blogspot.in/2011/05/excel-speed-up-data-entry.html

Some simple and interesting formulae on dates are:

1. To find out what day of the week the date appearing in cell B6 is, type =TEXT(B6,"dddd"). “Friday” appears which is the full form of the day of the week. On the other hand using =TEXT(B6,"ddd") (i.e. one “d’ less in the formula) gives “Fri” which is the abbreviated day.

2. Today’s date is displayed with “=TODAY()”. It is to be remembered that this displays the current date whenever the Excel sheet is opened and is useful when using a countdown or calculating the day before or after a specific event.

3. And if the current date is merely to be entered as a fixed entry, without being changed each time the Excel file is opened, press CTRL+; (semi-colon).

(This post is in response to a specific request by a reader. I do hope the requirement is fully met. I would be happy to respond further on this or any other issue).

5 comments:

Syed Zeeshan Ali said...

Great information
Technology, Free Software and Best Tutorial
Carry on your good work
God Bless You

Anonymous said...

Post writing is also a excitement, if you be familiar
with after that you can write otherwise it is complex
to write.

My webpage Safe Diets

Leona james said...

I would never like to fail out any chance to read out your listings.
best-tablet-reviews.weebly.com/

Tech Rotation said...

I do agree with all the ideas you’ve offered in your post. They’re very convincing and can certainly work. Still, the posts are too short for starters. May you please lengthen them a little from subsequent time? Thanks for the post.

Payoffers dotin said...

Earn from Ur Website or Blog thr PayOffers.in!

Hello,

Nice to e-meet you. A very warm greetings from PayOffers Publisher Team.

I am Sanaya Publisher Development Manager @ PayOffers Publisher Team.

I would like to introduce you and invite you to our platform, PayOffers.in which is one of the fastest growing Indian Publisher Network.

If you're looking for an excellent way to convert your Website / Blog visitors into revenue-generating customers, join the PayOffers.in Publisher Network today!


Why to join in PayOffers.in Indian Publisher Network?

* Highest payout Indian Lead, Sale, CPA, CPS, CPI Offers.
* Only Publisher Network pays Weekly to Publishers.
* Weekly payments trough Direct Bank Deposit,Paypal.com & Checks.
* Referral payouts.
* Best chance to make extra money from your website.

Join PayOffers.in and earn extra money from your Website / Blog

http://www.payoffers.in/affiliate_regi.aspx

If you have any questions in your mind please let us know and you can connect us on the mentioned email ID info@payoffers.in

I’m looking forward to helping you generate record-breaking profits!

Thanks for your time, hope to hear from you soon,
The team at PayOffers.in