In this article we will learn how to convert date time in (UTC/GMT) format.
To covert time into a serial (numerical) value, enter in cell A1: 2342 (this represents the time 23:42)
Enter the following formula in cell B1: =(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2))+0
The Result: 0.9875
Please note: The serial value for time ranges between 0 to 1; midnight=0, 24 hours=1
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
Wow! Moderator, Please delete this comment for shear lack of editing - the information is correct but grammatically I can tell it is past midnight.
An alternate way to do this is to enter a 24 hour time format, e.g. 06:00 or 23:42 into a cell say A1 and in the cell you want the numerical value just type in the formula "=A1" - after that just reformat to the desired to a number with the desired number of digits by right clicking and selecting, "Format Cells"
"If both are entered as standard Excel date / time values, then just subtract the end from the start, and you will have a value equal to the number of days that have elapsed.
For example, if the difference is 1.75, then 1.75 days have elapsed, equal to 32 hours.
You can format the answer cell to display your answer in whatever format makes most sense:
For example:
[hh]:mm
would give you 32:00 (hours).
See the reply two above for a more full explanation. "
"I have fields that have time start and another Time End:
I want to know how to have another field that says how many days and hours and minutes have passed.
Start End
1/1/2003 2:00 PM | 1/2/2003 3:00pm"
Your solution works well!
"If you have the hours:minutes (35:30) entered in cell (B2) and an hourly rate (15) entered in cell B3 then the actual value that is in B2 is (1.47916666666667) as a decimal or (35.5/24) to be precise
Since your units are out of sync (the time is entered as fraction of a day, but your rate is hourly) you need to bring them together.
Let's convert the hourly rate to a daily (24 hour) rate to give a dollar total of:
=B2*(B3*24) {=532.50}
Alternatively, we could convert the days to hours:
=(B2*24)*B3 = {532.50}
Both of them give $532.50 total of course.
Hope that helps, "
"I can add time together to give the total amount of hours and minutes worked in a week eg 35:30. If I work for $15 an hour how do I calculate this using my time calculation?
Reply: hours worked - allen from oakland, ca wrote on August 4, 2003 6:48 PM EST
Alan Posted on: 31-12-1969
Hi Allen,
If you enter those times in cells C2:D6 and I will assume you use standard (24 hour) notation, then the following formula entered in E2 and copied down should give you what you want:
=D2-C2
Depending on your cell formatting, that can show as a decimal or hours : mins, but fundamentally, the cells contain a number that is the portion of a day in each case giving the following (in hours:mins):
06:55
07:45
06:50
10:30
04:15
or the following (in decimals):
0.288194444
0.322916667
0.284722222
0.4375
0.177083333
If you then sum E2:E6 you would get:
1.510416667 (as a decimal) which is just over 1.5 days or just over 36 hours in total (36 hours, 15 mins to be precise).
You can of course format the sum cell to show the answer in whatever form you prefer (hours, days etc).
For example if you want to display it as 36:15 then use the custom number format:
[hh]:mm
If you want to convert the answers from fractions of a day to hours as units, just multiply by 24 (!).
In that case, the sum total would become 36.25 hours (=36 hrs, 15 mins).
Be aware though that excel would now assume this number is 36.25 days using its standard notation, so you need to take explicit control of the units if you go down that route.
Hope that helps, "
I can add time together to give the total amount of hours and minutes worked in a week eg 35:30. If I work for $15 an hour how do I calculate this using my time calculation?
Think you for your help, however I need my hand held , I don't understand how to write the formular, I put in my information in cell a1 & b1. and it did not work. Using the information above, would you please give me a actual example and caluate the daily hours worked.
"I, NEED A FORMULA THAT CALCULATES MY DAILY HOURS WORKED. EXAMPLE , I WORKED THE FOLLOWING DAYS / HOURS
MONDAY 10:15 AM TO 5.10 PM , TUESDAY 9:AM TO 4:45PM WEDNESDAY 8:10AM TO 3:00PM, THURSDAY 7:45AM TO 6:15PM & FRIDAY 9:45AM TO 2:00PM "