Android Century
  • Home
  • Android Zone
    • Android Apps
    • Android Games
    • Apps APk Files
    • Games Apk Files
    • Apps Hack Tricks
  • Reviews
  • Fantasy Zone
    • Entertainment
    • Quotes and Status
    • Life Style
    • Home Made Tips
    • Hair Care
    • Skin Care
    • Fantasy Tips
  • Tricks
    • Free Recharge
    • Free Internet
    • shopping Cashback
    • Recharge Cashback
  • Tech
  • Mobiles
  • Gadgets
  • News
  • How To's
  • Software
Breaking
Loading...

Featured post

How to Take Great Photos With Apple's iPhone X

Recent Posts

Labels

  • Android Apk Files
  • Android Apps
  • Android Games
  • Apps Apk Files
  • Entertainment
  • Fantasy Tips
  • Gadgets
  • Hair Care
  • HomeMade Tips
  • How To's
  • News
  • Quotes
  • Quotes & Status
  • Recharge Cashback
  • Recharge Promo Codes
  • Shopping Cashback
  • Technology
  • skin care
Home / How To's / Mastering Excel Date & Time: Serial numbers, Networkdays, Datevalue, and more

Mastering Excel Date & Time: Serial numbers, Networkdays, Datevalue, and more

Latest Govt. Jobs 01:00:00 How To's Edit
If you need to calculate dates in your spreadsheets, Excel uses its own unique system, which it calls Serial Numbers. Every date (month, day, year) and time (hour, minute, and second) has an equivalent Serial Number dating back to January 1, 1900 at 00:00:00 (hours, minutes, seconds). Not to be confused with Julian dates, which mainframes and many programming languages use, Excel serial numbers are unique to Microsoft products.
Open Excel and enter the numbers 1 through 5 in cells A3 through A7. Highlight the range, then from the Home tab’s Number group, select Short Date from the drop-down list. Notice that your numbers 1 through 5 have changed to the following dates: 1/1/1900 through 1/5/1900.
01 excel serial numbersJD Sartain
Now enter the dates in cells B3 through B7 for the first five days in May; 5/1/2016 through 5/5/2016. Highlight the range; then from the Home tab’s Number group, select General from the drop-down list. Notice that your five dates have changed to the following numbers: 42491 through 42495.
Your new PC needs these 15 free, excellent programs
These are the numeric equivalents of the first five days in May of 2016. Excel calls them serial numbers and uses them to calculate dates and times.
Next, enter the following formula in column C to convert Excel’s serial-number dates to Julian dates:
=RIGHT(YEAR(B1),2)& B1-DATE(YEAR(B1),1,0). This is a useful formula if you have to export your spreadsheet data with dates to another program such as a database or timestamp application.
This formula converts Julian dates back to serial dates: =DATE(1900+INT(Julian_Date/1000),1,MOD(Julian_Date,1000))

Number of days between two dates

Magnum P.I. charges $200 a day plus expenses for his services as a private investigator. He wants to know how much money he’ll earn if he works every day from February 21, 2016 through May 1, 2016. Instead of manually calculating this timeframe (accounting for number of days per month, Leap Year, etc.), you can use Excel to quickly solve this problem.
The formula for this calculation is simple: Just subtract the end date from the start date:
=SUM(B1-A1). The answer is 70 days. Now add Magnum’s daily rate to the end of this formula:
=SUM(B1-A1)*200 and you get the answer in one easy formula: $14,000.00. That works out to about $25 an hour—lousy wages even for the 1980s.
02 number of days between two datesJD Sartain
Now Magnum wants to know how much he’ll earn if he works the same dates, but takes weekends off. Enter this formula in C3: =NETWORKDAYS(A3,B3). The new answer is 50 days. Or select Date & Time from the Formulas tab, click NETWORKDAYS, and the Function Arguments window opens. Enter the start date and end date in the field boxes or click the corresponding cells (point to them) on the spreadsheet, then click OK. Same answer: 50 days. Now add Magnum’s daily rate to the end of this formula: =NETWORKDAYS(A3,B3)*200 and you get the new salary in one basic step: $10,000.00.
03 number of days between two dates excluding weekendsJD Sartain

Convert text dates to actual dates

When dates are copied into Excel, they’re often copied as text. Importing dates is a better option but, sometimes, even imported dates come in as text. When this happens, use the DATEVALUE function to convert the text dates to Excel serial numbers. To test this function, enter five dates—as text—in cells A5 through A9. Insert an apostrophe before each date, and that will convert the date to a text entry.
NOTE: When dates are copied or imported into Excel as text fields (from an ASCII file, database, or even a text file), the system automatically reads them as text; however, if you copy a date from MS Word (using copy/paste/text), Excel recognizes it as a date.
Move your cursor to B5. From the Formulas tab, select Date & Time > DATEVALUE. The Function Arguments screen appears. You do not have to re-enter the date in the Date_text field box. Just click the text-date in cell A3, and the Excel serial number appears in cell B5. Copy the formula down to B9.
04 convert text dates to actual datesJD Sartain
Now, all you have to do is format the cells to whichever date format you prefer. Highlight cells B5 through B9. From the Home tab’s Number group, select a number format from the General drop-down list. You may also click the tiny arrow in the bottom right corner of this group, and the Format Cells window opens. Select Date from the Category (left panel). Scroll through the date options in the Type panel, and choose the one that fits your project. Now your Excel serial numbers are displayed as actual dates, which means you can use formulas to make calculations.
05 change serial numbers to date formatsJD Sartain

Serial numbers for all date formats

All businesses use databases and spreadsheets to calculate dates and times daily for payroll, expenses, inventory, sales reports, accounting, and more.
06 the now function provides todays dateJD Sartain
Excel provides serial numbers for all of the date and time formats, from months down to individual seconds. See for yourself: Place your cursor in cell A1. Go to Formulas > Functions and select Date & Time > NOW. Then click OK in the popup window.
From the Home tab’s Cells group, select Format > Format Cells. In the Format Cells window, select a custom date format with minutes and seconds, then click OK.
Next, convert the date to an Excel serial number (as we did above, select the General format from the Home tab’s Number group).
The following formulas convert the serial number to the weekday, month, year, hour, minutes, and seconds. Here’s how.
Go to Formulas > Functions Library and select Date & Time > DAY. When the Function Arguments window opens, point to cell A1 or enter A1 in the Serial Number field box. Excel then displays just the day of the month—in this case, the 9th.
07 select a custom date format with minutes secondsJD Sartain
Select the following formulas from the Formulas tab, Functions Library group, Date & Time for cells B2 through B6: MONTH, YEAR, HOUR, MINUTE, SECOND, or enter the following formulas:
=MONTH(A1) returns today’s month
=YEAR(A1) returns today’s year
=HOUR(A1) returns today’s hour
=MINUTE(A1) returns today’s minute
=SECOND(A1) returns today’s second
08 serial numbers for all date formatsJD Sartain
All of these formulas can be used separately or with other Date & Time functions to calculate everything from days per quarter; to how many weeks or days between now and Christmas; to how many hours in the month of February on a Leap Year.

Excel Windows vs. Excel Mac

Windows Excel uses the 1900 date system (a carry-over from Lotus 1-2-3), while Excel for the Apple Mac uses the 1904 date system. So the serial number 1 in Windows Excel equals 1/1/1900, 12:00 am, while in Mac Excel it’s 1/2/1904, 12:00 am. Seems like Microsoft should have fixed this by now, but it’s still a problem. Transferring files between the two programs should work because the date system is stored in each file. But copy-and-paste is a different story. Different date systems on different platforms will result in incorrect dates, which will be off by four years and one day.
To make the Apple Mac’s Excel 1904 date system compatible with the Windows Excel 1900 date system:
09 convert mac excel dates to windows excel datesJD Sartain
From the File tab’s Backstage menu, select Options > Advanced. Scroll down the window until you see When Calculating this Workbook (the 10th subhead down the page). Check the box that says: Use 1904 Date System, then click OK.
There are a dozen (or so) more of these type of functions in Excel. Stay tuned for Part Two, which covers the remaining Date & Time formulas with examples.
Share on Facebook Share on Twitter Share on Google Plus

RELATED POSTS


How to get BookMyShow Instant Disco...

How To Get Free Recharge And Money ...

How to Sync Your Contacts Between A...
Mastering Excel Date & Time: Serial numbers, Networkdays, Datevalue, and more Mastering Excel Date & Time: Serial numbers, Networkdays, Datevalue, and more Reviewed by Latest Govt. Jobs on 01:00:00 Rating: 5

0 comments:

Post a Comment

Newer Post Older Post Home
Subscribe to: Post Comments ( Atom )

Search This Blog

TEST BOOK FOR GOVT ENTRANCE TEST

TEST BOOK FOR GOVT ENTRANCE TEST
Find All Latest book for preparation of SSC,RAILWAYBANK PO,RBI,BANK CLERK,GATE ME,GATE CE are available here in less prices, to check out the books click here

Translate

  • Popular Post
  • Random posts
  • Category

Popular Posts

  • Teen Patti Offer 2018: Refer and Earn Flipkart Vouchers Free
    Teen Patti Offer 2018: Refer and Earn Flipkart Vouchers Free
    Teen Patti Refer & Earn Offer:  Hey Guys! Today I make an article about Teen Patti Referral ...
  • Taotronics TT-BH22 Headphones Review
    We make it a addiction to now not look up pricing of a product sooner than reviewing and if ...
  • Reliance Jio to offer sharp tariff discounts for customers signing up by March-end
    Reliance Industries' Jio unit will charge a tariff for its services from April, but will offer ...
  • Pentagon strongly condemns North Korea missile test
    The Pentagon on Monday strongly condemned North Korea’s latest missile test, adding that the ...
  • Fitbit Zip 2017 review
    Fitbit PROS: Clever, accurate tracking Expandable online service Integration with 3rd ...
  • Bank wallets growing faster than e-wallets
    In the  bank  versus  e-wallets  sweepstakes,  lenders  have now gained lost ground. As of ...

Random Posts

  • Flat Rs. 5 Cashback on recharge or bill payment of Rs.10 or more.
    Flat Rs. 5 Cashback on recharge or bill payment of Rs.10 or more.
    23.02.2018 - 0 Comments
    Flat Rs. 5 Cashback on recharge or bill payment of Rs.10 or more. Code can be used 10 times every…
  • Amazon Fire TV Stick review
    Amazon Fire TV Stick review
    17.03.2017 - 0 Comments
    Amazon VERDICT If you've already bought into Amazon's ecosphere, the Fire TV Stick will be the…
  • Top/Best Custom ROMs for Android Device after Rooting via KingoRoot
    Top/Best Custom ROMs for Android Device after Rooting via KingoRoot
    18.02.2017 - 0 Comments
    Flasing a custom ROM may be the main reason for many users to root their device via KingoRoot.…
  • Garmin Vivofit 4 Review
    Garmin Vivofit 4 Review
    03.02.2018 - 0 Comments
    Looking For a Fitness band that will track your basic activity? Garmin thinks the Vivofit Four would be the…
  • Redmi 4A lasts for all of 4 minutes on launch day
    Redmi 4A lasts for all of 4 minutes on launch day
    25.03.2017 - 0 Comments
    Amazon India sold 2.5 lakh units of Xiaomi’s Redmi 4A in under four minutes during its…

Labels

Android Apk Files Android Apps Android Games Apps Apk Files Apps Hack Tricks Entertainment Free Internet Freecharge Gadgets Games Apk Files How To's Laptops Guide Mobiles Reviews Technology Viral's android zone free recharge

Entertainment

Tricks

Popular Posts

  • Teen Patti Offer 2018: Refer and Earn Flipkart Vouchers Free
    Teen Patti Offer 2018: Refer and Earn Flipkart Vouchers Free
    Teen Patti Refer & ...
  • Taotronics TT-BH22 Headphones Review
    We make it a ...
  • Bank wallets growing faster than e-wallets
    In the  bank ...
  • Reliance Jio to offer sharp tariff discounts for customers signing up by March-end
    Reliance Industries' Jio unit will charge a tariff for its services from April, but will offer ...
  • Hands-on with the home windows 10 Creators update for the Xbox One: Beam recreation streaming arrives
    The Windows ...
  • Pentagon strongly condemns North Korea missile test
    The Pentagon on ...
  • Fitbit Zip 2017 review
    Fitbit PROS ...

Random Posts

  • Google Play Books 3.7.47 (30747) APK Download
    Google Play Books 3.7.47 (30747) APK Download
    29.02.2016 - 0 Comments
    Download Google Play Books v3.7.47In Books & Reference by Top Developer Google…
  • SAMSUNG WA75F4H6QWP 7.5 KG FULLY AUTOMATIC TOP LOAD WASHING MACHINE
    SAMSUNG WA75F4H6QWP 7.5 KG FULLY AUTOMATIC TOP LOAD WASHING MACHINE
    22.11.2016 - 0 Comments
    SUMMARY These days’ households cannot find the right help you wash and clean clothes. So technology has…
  • How to Root Micromax in One Click with KingoRoot Android
    How to Root Micromax in One Click with KingoRoot Android
    18.02.2017 - 0 Comments
    How to root any Micromax devices? If you got a Micromax, KingoRoot will offer you the best and…
  • Comparison: iPhone SE Vs Galaxy S7 Mini [Go Android Authority.com]
    Comparison: iPhone SE Vs Galaxy S7 Mini [Go Android Authority.com]
    22.03.2016 - 0 Comments
    As we all know, yesterday, Apple announced its new iPhone better known as iPhone SE. It is a smaller…
  • FiiO launches F9 PRO Triple Driver IEM headphones at Rs 12,990
    FiiO launches F9 PRO Triple Driver IEM headphones at Rs 12,990
    18.01.2018 - 0 Comments
    The F9 PRO Triple Drive IEM contains a dynamic driver & 2 balanced armatures on every channel. FiiO has…

Most Popular

  • Teen Patti Offer 2018: Refer and Earn Flipkart Vouchers Free
    Teen Patti Offer 2018: Refer and Earn Flipkart Vouchers Free
    Teen Patti Refer & ...
  • SAMSUNG GALAXY J7 (2016) REVIEWS
    SAMSUNG GALAXY J7 (2016) REVIEWS
    SAMSUNG GALAXY J ...
  • Top 5 Best SmartPhones under 7000 Rs (March 2017)
    Looking for the ...
  • Apple, IBM, Cisco are huge because of Indians, do not deny them H-1B visa: RBI Governor Urjit Patel
    ...
  • SAMSUNG GALAXY J7 (2016) Specifications
    SAMSUNG GALAXY J ...
  • BlackBerry Teases Marshmallow Beta Testing for Priv by Next Week
    Blackberry ...
  • LG Q6 Review
    LG Q6 Review
    2017 is ...

Contact Form

Name

Email *

Message *

Offers Zone

Created By Android Century Distributed by Android Century
  • Home
  • About us
  • Contact us
  • Privacy policy
  • Terms of use
  • Advertise here
Subscribe Via Email Subscribe To Android Century By Email And Get Free Updates. ;-)


Your email address is safe with us!