Working Fully With the Alline Once Again
Computing Time with Excel Formulas
How often have y'all looked at your watch and typed the current fourth dimension into your worksheet? Or tried to convert from ane time zone to another and produced an error? Or wondered why a time shows upward looking like "0.9285"?
Fortunately, these questions are piece of cake to answer once you sympathize how Excel time formulas work. This commodity:
-
- Explains some basic time formulas such as Now() and Fourth dimension(),
- Shows how to add and subtract times, and
- Uses these in 2 sample workbooks: a timesheet calculator and a time zone converter.
The NOW() Role
NOW() returns the current date and time from your estimator.
You can reformat the result by correct-clicking the cell, choosing "Format Cells," and picking a unlike time format.
If you lot want merely the time and not the date, you lot can either format it every bit a fourth dimension and pretend the date doesn't be, or you can utilize "=Now()-TODAY()", which subtracts the date portion and leaves simply the fourth dimension portion. Regardless of how yous format the display, nonetheless, the underlying value remains constant until you recalculate.
Whenever yous recalculate (or whenever Excel recalculates automatically considering other cells take changed), the function pulls the time as of that moment. Use this role when you need an always-updated electric current fourth dimension in your calculations.
NOTE: Someone who opens the worksheet in six months will see her own electric current date and time in this field, non the engagement and fourth dimension that you saved the workbook.
The Shortcut Control-Shift-;
When you demand to save a permanent record of the current fourth dimension, concur down the Control primal and the Shift key while you type a semicolon (;). Similar Now(), it returns the current system fourth dimension. Unlike NOW(), however, it stamps the fourth dimension as a stock-still value. You can modify the format, but the value is e'er the same.
Similarly, you tin save the current engagement by property downwards the Control primal (simply not the Shift key) and typing a semicolon.
Because information technology saves the value and not an updateable function, someone who opens the worksheet in 6 months will see the appointment and time that yous stamped on the page, non her own current date and fourth dimension.
The Time() Role
Both the At present() office and the Ctrl-Shift-; shortcut return the current organisation fourth dimension. What if you demand to shop a value for some other fourth dimension?
When you demand to convert a specific number of hours, minutes, and seconds to an Excel time, apply the TIME() function with the parameters the numbers of hours, minutes, and seconds. Typing "=TIME(xiv, 30, 10)" produces:
Be sure to enter the hours using a 24-hour clock.
Adding and Subtracting Times
When y'all need to add or decrease a fixed number of hours, minutes, or seconds to a time value, only add together or subtract the way you would any decimals. If you add together 4 hours to 11:thirty AM, you get iii:xxx PM:
To empathise time arithmetics more than fully, you need to empathize outset how Excel stores times.
Try this: in an empty cell, type "=At present()" and striking <ENTER>. The result shows the current date and time, probably formatted like this:
6/26/2014 9:57
Next, correct-click the cell, cull "Format Cells," and pick "Full general." The result should wait something like this:
41816.41498
This is how Excel stores engagement/times—the date is the number to the left of the decimal, and time is the decimal portion of the number, where 0 corresponds to 12:00 AM, ane is midnight, and every other time of the day falls somewhere in between. Times are decimals values between 0 and 1.
To convert a time to its decimal value, split the hours (using the 24-hour clock) past 24 (the number of hours in a full twenty-four hour period):
- 12:00:00 PM (Noon) is exactly halfway through the day, and so the decimal value is 1 / 2 = 0.5.
- 6:00:00 AM is i-fourth of the twenty-four hour period, so the decimal value is one / 4 = 0.25.
- 9:00:00 PM corresponds to 21:00:00, so the decimal value is 21 / 24 = 0.875.
On the other hand, to convert a time decimal value to the number of hours, multiply past 24.
Because times are simply numbers between 0 and 1, you can do any arithmetics on times, only as you would with any other decimal numbers. Add together them, subtract them, multiply them past ii, divide them by four—just like any other decimal numbers.
Simply there are a couple of warnings as you go into engagement arithmetic.
Why Does 15 hours + 18 hours = 9 hours?
Attempt this, using a 24-hour format:
Right-click on Cells A1 through A3, choose "Format Cells," and select "General."
This shows that Excel did, in fact, add together the two time values, which resulted in a number greater than i, significant more than a total mean solar day. Similar an odometer, however, Excel'south standard time formats curlicue over and show only the decimal portion—0.375, which equals nine:00:00.
When you need to show hours greater than 24, such as when adding upwardly hours from a timesheet, choose a time format that displays the larger numbers:
When working with customer formats, surround the hours with square brackets ([]):
Applying that format to this case produces 33 hours, as expected:
Why Does 4 hours – 12 hours Produce an Error?
This situation arises when yous're calculating how far in advance you demand to begin a job so that it will be completed by the target fourth dimension. It arises likewise in certain time zone conversions that cross over into the prior mean solar day—such equally computing London time when it'south v:00 AM in Tokyo. (See the time zone converter below and the mistake-trapping used in the calculation.)
Unfortunately, Excel does non like negative times, and then you'll need to innovate an error-trapping formula. Whenever subtracting times could result in a negative time, wrap information technology in an IF() statement which adds 1 to the result. (In cases which might event in something prior to ane 24-hour interval back, you'll demand to adjust your formula even further.)
Finally, you're prepared for useful applications of these formulas.
Timesheet Figurer
The attached timesheet calculator uses simple fourth dimension subtraction to determine the number of hours worked in each segment: fourth dimension out – time in. The format for hours worked is "[h]:mm," which ignores seconds and does non brandish AM or PM. The calculation traps for negative numbers using an IF() statement: if the time in is greater than the time out, then it displays the message, "Fourth dimension out must be later than time in."
Next, the calculator adds the elapsing of each segment in each solar day to attain a daily total. Over again, this is elementary time arithmetic: "=E8 + E9".
The weekly total sums the daily totals and uses a format of "[h]:mm." The foursquare brackets around the hours let this formula to show values greater than 24 hours.
Finally, the overtime formula determines whether the number of weekly hours is greater than twoscore. Recall that 40 must start be converted into a fraction of a solar day by dividing past 24, so the formula compares the hours worked to 40/24. If greater, and so the overtime formula subtracts 40/24 from the hours worked and returns the difference. If less, then the overtime formula returns 0:00.
Time Zone Converter
In the fastened time zone converter enter a starting time and time zone in Columns A and B (in the gold cells).
Cavalcade C converts the local time to Greenwich Mean Time, besides known as UTC (Coordinated Universal Time), past subtracting the differential from GMT. Call up that the differential (in hours) must be converted to a decimal first by dividing by 24. Considering this could result in a negative time if the local time is far due east and is early in the morning, the formula uses an IF() statement, adding a one if the result would be negative.
In Columns D, F, and H, enter target fourth dimension zones for the conversion. Columns E, G, and I calculate the target times by starting with the GMT base and adding the differential from GMT.
All of these calculations employ data from the tabular array on the "Fourth dimension Zone Data" tab. Experience free to add together records, for this tabular array is far from including all worldwide time zones. Note that you can define the time zone abbreviation in Column A using whatever format that you like. The sample data included shows some with abbreviations (GMT, EST, PDT, etc.) and others using cities. Any values yous enter here volition testify upwardly in the selection list for entering data in Columns B, D, F, and H of the converter.
Next Steps
This is just the beginning of calculating formulas using times. From this point, you lot can develop far more complicated formulas, such equally determining the average time required to consummate a job, the maximum time betwixt phone calls, etc. As long equally you call up that times are but decimal numbers that represent portions of a day, you can work through whatever remaining calculations. You have the tools, and you know how to apply them. Enjoy!
Source: https://www.pryor.com/blog/calculating-time-with-excel-formulas/
0 Response to "Working Fully With the Alline Once Again"
Post a Comment