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

If you need to calculate dates in your spreadsheets, Stand out uses its own unique system, which it calls Serial Numbers. All date (month, day, class) and time (hour, microscopic, and second) has an equal Serial Number dating back to January 1, 1900 at 00:00:00 (hours, minutes, seconds). Not to be confused with General dates, which mainframes and many another programming languages use, Stand out music Numbers are unique to Microsoft products.

Open Excel and enter the numbers 1 through 5 in cells A3 through A7. Foreground the range, then from the Interior tabloid's Number group, pick out Curtal Date from the drop-down list. Mark that your numbers 1 through 5 receive changed to the following dates: 1/1/1900 through 1/5/1900.

JD Sartain

Now enter the dates in cells B3 through B7 for the first Little Phoeb days in May; 5/1/2016 done 5/5/2016. High spot the range of mountains; and so from the Home tab's Number group, select General from the put down-down list. Notice that your quintuplet dates have changed to the following numbers racket: 42491 finished 42495.

These are the numeric equivalents of the first Little Phoeb years 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 National leader dates:

=RIGHT(YEAR(B1),2)&adenosine monophosphate; 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 confidential tec. He wants to know how much money he'll garner if helium works every day from February 21, 2016 through May 1, 2016. Instead of manually calculative this timeframe (accounting for number of days per month, Bissextile year, etc.), you tin can use Excel to quickly lick this trouble.

The formula for this calculation is dolabriform: Just deduct the stop date from the start date:

=SUM(B1-A1). The answer is 70 years. Right away tally Magnum's daily charge per unit to the end of this formula:

=Pith(B1-A1)*200 and you get the answer in one easy formula: $14,000.00. That works dead set astir $25 an hour—lousy payoff even for the 1980s.

JD Sartain

Right away Magnum wants to know how very much he'll realize if helium works the same dates, simply takes weekends off. Enter this formula in C3: =NETWORKDAYS(A3,B3). The new answer is 50 days. Oregon select Day of the month & Time from the Formulas tab, click NETWORKDAYS, and the Function Arguments windowpane opens. Enter the start date and end date in the field boxes Beaver State click the corresponding cells (point in time to them) on the spreadsheet, so get through OK. Same answer: 50 days. Now ADD Magnum's daily rate to the end of this normal: =NETWORKDAYS(A3,B3)*200 and you get the new salary in same basic footfall: $10,000.00.

JD Sartain

Convert text dates to actual dates

When dates are derived into Surpass, they're often copied as text. Importation dates is a better selection but, sometimes, even imported dates come in equally text edition. When this happens, use the DATEVALUE function to convert the text dates to Excel serial numbers. To exam this function, enter five dates—equally text—in cells A5 through A9. Tuck an apostrophe before each date, and that will convert the date to a text entry.

NOTE: When dates are copied or strange into Excel as text Fields (from an ASCII file, database, or even a text lodge), the system automatically reads them atomic number 3 textual matter; however, if you copy a go out from MS Word (using copy/glue/text), Stand out recognizes it as a date.

Strike your pointer to B5. From the Formulas tab, select Date & Fourth dimension > DATEVALUE. The Routine Arguments riddle appears. You act not have to atomic number 75-record the date in the Date_text force field box. Just click the text-go out in cell A3, and the Surpass serial number appears in cell B5. Re-create the chemical formula down to B9.

JD Sartain

Now, entirely you have to do is format the cells to whichever date format you prefer. Play up cells B5 through and through B9. From the Home tab's Number group, select a number initialise from the General drop-down list. You may also click the tiny arrow in the bottom right corner of this group, and the Formatting Cells window opens. Select Engagement from the Category (left panel). Scroll through the go steady options in the Type panel, and pick out the one that fits your project. Now your Excel serial numbers pool are displayed as literal dates, which means you can apply formulas to work calculations.

JD Sartain

Serial numbers for completely date formats

All businesses use databases and spreadsheets to account dates and times daily for payroll, expenses, stock list, gross sales reports, accounting, and more.

JD Sartain

Excel provides serial numbers for all of the day of the month and time formats, from months down to individual seconds. Run across for yourself: Pose your pointer in cell A1. Go to Formulas > Functions and select Date & Meter > NOW. So click OK in the popup window.

From the Home tab's Cells grouping, select Format > Format Cells. In the Data formatting Cells window, take a custom date format with minutes and seconds, then click OK.

Next, change the date to an Excel order number (as we did above, select the General format from the House tab's Number group).

The pursuit formulas convert the serial number to the weekday, month, year, hour, minutes, and seconds. Hera's how.

Go to Formulas > Functions Library and select Date & Time > DAY. When the Function Arguments windowpane opens, point to cell A1 or enter A1 in the Serial Issue athletic field box. Excel then displays just the day of the month—in this case, the 9th.

JD Sartain

Select the following formulas from the Formulas tab, Functions Library aggroup, Go steady & Metre for cells B2 through B6: MONTH, YEAR, HOUR, MINUTE, SECOND, or enter the chase formulas:

=MONTH(A1) returns today's month

=YEAR(A1) returns today's year

=Hr(A1) returns nowadays's time of day

=Arcminute(A1) returns today's minute

=SECOND(A1) returns nowadays's second

JD Sartain

All of these formulas can be in use separately or with other Date & Time functions to calculate everything from days per quarter; to how many weeks or days between straightaway and Christmas; to how many hours in the calendar month of February on a Bissextile year.

Surpass Windows vs. Stand out Mac

Windows Stand out uses the 1900 go out system (a extend-o'er from White 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 the 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. Other date systems on contrary platforms will result in incorrect dates, which volition be off by four years and one day.

To make the Malus pumila Mackintosh's Surpass 1904 go steady system compatible with the Windows Excel 1900 date system:

JD Sartain

From the Indian file tab's Wing menu, select Options > High-tech. Gyre down the window until you see When Hard this Workbook (the 10th subhead pull down the paginate). Check mark the box that says: Use 1904 Date System, then click O.k..

There are a dozen (some) more of these type of functions in Surpass. Stay tuned for Component part Two, which covers the unexhausted Go out & Time formulas with examples.

Source: https://www.pcworld.com/article/414682/mastering-excel-date-time-serial-numbers-networkdays-datevalue-and-more.html

Posted by: reyessincen.blogspot.com

Share this post