Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Time Conversion: Convert A Time From Hours/Minutes To Hours/Tenths


When I am converting a time from Hours/Minutes to Hours/Tenths, Excel is not converting it consitantely. EXAMPLE: 1:15 = 1.25. When I format the cell to present only one place past the decimal point, sometimes the cell will round up to 1.3, and other times it will round down to 1.2. What am I missing?


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Convert Total Time To Days & Hours, Minutes
I have a column of tasks that take a certain amount of time to complete formated as h:mm:ss. I want to total the column and convert the total to days, hours and minutes. Is that posible and if so how do I configure a formula and format the cell? example:

task 1 54:00:00
task 2 20:45:00
task 3 27:05:20
task 4 51:10:45
total 153:01:05

How many days, hours and minutes?

View Replies!   View Related
Format Time Cell For Greater Than 24 Hours: Hours & Minutes Only
Format Time Cell For Greater Than 24 Hours: Hours & Minutes Only .....

View Replies!   View Related
Adding Time :: By Hours And Minutes
If i have a start time of 5:00 am and a finishing time of 4:25 pm, what function do i use to get the total time of 11 hours & 25 minutes?

View Replies!   View Related
Time Format Why I Enter Colon Between The Hours And Minutes
I have used the format [hh]:mm in a cell for 24 hr clock calculations. Why do I have to enter the numbers with a colon when I populate the cells? Is there a way to set it up so I just type in the four numbers and the colon between the hours and minutes populates itself?

View Replies!   View Related
Formula To Deal With Time, Hours & Minutes
I've got a worksheet that deals with dates and time. Similar to a checkbook, I need to take the balance and subtract what's taken from that. But the writer copied this from another software program and it's in this format. How do I convert it to a time format? Or is that what I really need? Right now the taken and balance are both in a general format.

I just need to know on any of the given days what the balance of this person's time was in hours and minutes.

View Replies!   View Related
Time Card & Sum Hours And Minutes
Just basic enter a start time in column A a finish time in column B and column C gives you total in hours and minutes and then maybe a way to sum those hours and minutes. how to do "Math" on time and how it should be entered (formatted) for it to work properly.

View Replies!   View Related
Calculate Time Differrence In Days Hours Minutes
I have been trying to write a formula to calculate the differrence in days, hours or minutes between 2 cells in Excel.

Here is an example of the two cells

06/08/2011 00:0006/08/2011 12:19

View Replies!   View Related
Subtract Date/time And Get Elapses Days, Hours, Minutes, Etc
I want a forumal to do in excel.

Assuming that a workday is from 8:30am to 5:00pm. (also need to not calcualte Saturday and Sunday but this example doesn't show it).

Wednesday, 7/18/07 at 4:00pm (minus) Friday, 7/20/07 at 9:00am (should equal) 10 hours or 1 day, 1 hour and 30 minutes.

Basically what I want to do it determine how much work time has elapsed from the time a message was left on voicemail to the time that someone picks that message up. We only want to assume that a message can be retrieved duruing business hours but a message can be left at anytime!

View Replies!   View Related
Break Time Difference Into Minutes & Military Hours
if someone called me at 4:55pm and ended the call at 5:10pm, the whole call lasted 15 minutes. So, I want to show on excel that the call lasted 5 minutes in the 16th hour of the day (4:55pm) and 10 minutes in the 17th hour of the day (5:10pm), for a total of 15 minutes.

View Replies!   View Related
Format Time To Show Hours, Minutes & Seconds
is there a way in VB to make it fast, and tidy, clear the contents of all unlocked cells on one sheet ?

View Replies!   View Related
Formula To Comput Hours:minutes Between Time On Different Dates A Couple Of Days Apart
Im trying to figure out the formula to comput hours:minutes between time on different dates a couple of days apart.

B12 = Date
B15 = Time (on date in B12)

Previous date and time are in AA9. I have been trying to us =SUM(B12+B15)-(AA9).

View Replies!   View Related
Convert Time Decimals To Hours Mins
I am creating a very quick holiday excel sheet where people can fill in the hours they want on which days.

I have a summery sheet too.

So if someone wants to take a full day which is 7.4 hours. How would this be represented as Hours and minutes?

View Replies!   View Related
Convert Decimal Hours To True Time
formulae that converts % into time and visa-versa.

I.e 1.5 hrs = 1.30 hours ( i.e 90mins)
or the other way round
1.30 hours = 1.5 hrs..

View Replies!   View Related
Time Scheduling: Take Out A 30 Min Break If The Hours Worked Is Over 6 Hours
I am making a schedule and I would like it to take out a 30 min break if the hours worked is over 6 hours.

I have so far

A B

1 11:00 7:30

=24*(B1-A1)

Gives me 8 hours, I would like it to subtract the 30 minutes only ifthe sum is over 6 and not alter the sum if it is under 6.


View Replies!   View Related
Convert 3786 Minutes To Day:hours:minutes
I'm trying to convert 3786 minutes to day:hours:minutes. So divided it by 1440 which is 2.63... but I want this displayed in the worksheet as 2 days 1 hour and 3 minutes (02:01:03), I just can't seem to get it to work and it seems quite simple... but I'm missing something.... I was trying a custom format like dd:hh:mm or [d]:hh:mm and I was also trying a convert function and =day/1440+hour +minute

View Replies!   View Related
Convert Hours To Minutes. Display Total Minutes
I have a formula which will calculate the number of hours and minutes between two military times. I would like it to calculate the total number of minutes instead of hours and minutes. I have uploaded a small example of what i have so far.

View Replies!   View Related
Convert Decimal Hours And Minutes To Minutes
What formula will convert 4.50 to 530 minutes ( "Decimal Time" )

another example 16.50 to 1250 minutes.

View Replies!   View Related
Convert Hours & Minutes To Minutes `
I have a worksheet which I am trying to format as a template which includes inputting start times and end times of work and calculating how many minutes are taken to do the job. I just can seem to find the correct formula.

View Replies!   View Related
Convert Hours To Minutes
I have a database which shows a time some one came into work and a time they left. Formatted as HH:MM. I can minus the time they left from the time they came in...to work out how many hours they worked. However, whenever i want to work out how many mintes it is (time left - time in * 60). It does not seem to work.

Example

A1 - Time In 09:00
A2 - Time Out 17:00

A3 - Hours = 8 (A2-A1)

A4 - Mintes should be 480. (A3*60)

View Replies!   View Related
Convert Minutes Into Decimal Hours
How to I convert minutes into the equivalent fractional hours?

View Replies!   View Related
Convert Hours To Minutes And Seconds
I am trying to use the Mod function to convert the following:

I have time in hours in A1 as 12.66 and I need it to convert to 12 hours in A2, 39 minutes in A3, and 36 seconds in A4. What formula can I use to achieve this?


View Replies!   View Related
Subtract Date & Time To Show Total Hours & Minutes
How can I work out the following in hours an minutes: -

Cell A1 = 12th May 2007 20:00
Cell B1 - 14th May 2007 14:30

or should I put the dates and times in seperate cells? I would like the answer to show 42 hours 30 minutes

View Replies!   View Related
Format Total Hours To Days, Hours & Minutes
1) The output of an excel duration is : 22.00:8.00:25.00 ( day:hour:minutes ) - excel cannot average and work with this number format

2) resolution - =(LEFT(L2,4))+MID(L2, FIND(":",L2)+1,4)/24+MID(L2, FIND(":",L2,7)+1,4)/1440 as an array and Custom Format the cell as [h]:mm - works perfectly.

Q: to be conistent, the initial reporting is dd:hh:mm and then I convert to hh:mm so that excel can process the data. How can I convert from hh:mm to dd:hh:mm so that the excel report can be consistent in presenting the data to senior management?

example attached.

View Replies!   View Related
Convert Number To Hours & Minutes
How can I convert an ordinary number i.e. 3.45 to Hours & Minutes?

A colleague has columns of data that when summed are all wrong i.e. 8.65 instead of 9 Hours 5 minutes.

View Replies!   View Related
Subtract Start Time From Finish Time Return Hours Worked As Number
I have a user form with textBox1 = start time (entered as "[h]:mm") and text Box2 = finish time (entered as "[h]:mm"). I would like textBox3 to display the difference between the start time and finish time as a general number!

For example
Start time: 21:00
Finish time: 06:30
Hours worked: 9.50

Start time: 12:30
Finish time: 23:00
Hours worked: 10.50

View Replies!   View Related
Table Which Shows Time Started, Time Finished And Then A Total For Hours That Day, Then That Week
i am looking to do a table which shows time started, time finished and then a total for hours that day, then that week.

Start 08:00
End 16:00
Total 8 hrs.

How can I get the total to display as 8 hrs? not 08:00? When I change the format to "number" it shows 0.33?

View Replies!   View Related
Calculating Time And A Half And Double Time (After 40 Hours)
I'm battling to come up with the formula to calculate time and a half and double time. My boss wants me to show the overtime worked AFTER 40 hours has been worked. I have attached a copy of my spreadsheet.

View Replies!   View Related
Count Hours (time) Between Four Time Criteria
I am struggling to find a formula to calculate hour:minutes between the following:

Cell
A1= 04:00
B2= 17:00

I am wanting to calculate the hours between 06:00 and 16:00 and then the hours between 16:00 and 06:00 the next day

ie: 06:00-16:00 = 12;
16:00 - 0600 = 3

View Replies!   View Related
Adding Hours To Time To Get New Time
I have a started time of say 8am from cell B3. I want to know what time it will be when I add a full number (hours) from another cell. For example A3+B4 where A3 is 8:00 A.M. and B4 is 4.04. I'm looking to get the a result of 12:04 P.M.

View Replies!   View Related
Convert Decimal Minutes & Seconds To Days & Hours
I have two columns A and B. Column A contains problem title and column B is duration minutes. In column B we enter always min and seconds it took us to resolve certain issues (22.15 = 22 minutes and 15 seconds) . At the end of the month I need to run a report for my manager to show her by problem title how long it took my group to resolve issues and it should output as Hours/min/days.

View Replies!   View Related
"Time" Function To Find Time&date - ?hours
I m Trying to add some features to it and run into an issue. Here are the fields we are working with!

A1= 13:30:00 17/12/2008
(in other words 1:30pm on date given) - Need to know proper cell number format!!

B1= 23:00:00
(this represents 23 hours) - format for cell is [h]:mm:ss

C1= Unknown function
(This field is my problem!!)
Answer here SHOULD be "14:30:00 16/12/2008"

I need a function for C1 (using A1 and B1) that will give me the time and date 23 hours earlier or however many hours:mins:secs is specified in B1! Also need to know what number format to use for both A1 and C1

View Replies!   View Related
Convert Hours To Fractions Of Hours
I am attempting to convert a spreadsheet of times (listed in the format 06:15:39.62, where 06 is the hour, 15 is the minutes, 39 is the seconds, and .62 is in truncated miliseconds) into fractions of hours (so, 6.25 [NOT 6:25!]). I've so far been doing it manually for each value, which is quite tedious (doing basic division of seconds and minutes into hours, to find the fraction) but I'd like a single formula which I can then apply to the whole spreadsheet.

View Replies!   View Related
Convert Second To Hours And Mins (over 24 Hours)
i need to convert second to Hours and mins and can do so using:

View Replies!   View Related
Convert Seconds Into Hours, Minutes & Seconds
I have a spreadsheet that imports data relating to phone call durations. The information imports as a total number of seconds taken.

What I need to do is to change that number of seconds into hours, minutes and seconds.

Changing the format of the cells doesn't work.

I've tried everything I can think of, and run out of ideas.

example:

23 needs changing to 00:00:23
96 needs changing to 00:01:36
268 to 00:04:28
9374 to 02:35:14

View Replies!   View Related
Convert Hours, Minutes And Seconds Into Total Seconds
I trying to figure a formula to convert time on a phone call eg. 01:01:21 into total seconds (3661). Phonecalls will never be more than an hour long but the spreadsheet I will be supplied with (havn't got it yet!) will display them in the 00:00:00 format.

View Replies!   View Related
Hours:minutes:seconds Convert To Seconds
I am trying to convert a number of "hours" "minutes" and "seconds", to give me a result in seconds only, in order that I may then financially cost the amount of time spent on a task. (A time and motion costing exercise)

Example:

1119:48:06
Represents 1119hours:48minutes:36seconds spent on a task.

I can manually convert this to 4031286 seconds, but it just takes too much time !!!(sorry!)

The data is extracted from a database which is unable to split the hours,minutes,seconds into seperate fields, which prevents me from using three cells in excel.

View Replies!   View Related
Convert Seconds Into Hours:Minutes:Seconds
Is there an easy way of converting seconds from using a time into hours, minutes, seconds

View Replies!   View Related
Convert Time To Minutes In Userform
I know how to do this in a worksheet: =(b1-a1)*1440. B1 being the end time and A1 being the start time. I have a userform where the start time and end time are entered in text boxes. txtstart1 and txtend1. I would like the result to show up in txtmin1. Here is my code that doesnt work. I tried to convert code from a non-time sheet of mine. Dim as Integer may be the problem, I just learn as I go, and so far have only dealt with Integers.

View Replies!   View Related
Making Time Roll Over After 24 Hours
I'm creating a calculator to determine the distance & travel time on a xy grid for a game. I'll share what I have so far to display the big picture. Hereís what Iíve accomplished so far:

#1) Cells A1 & B1 are dynamic, they are frequently changed so distance and time may be displayed. Cells A2 & B2 do not change.

#2) I use the following formula to determine the distance between cells, displayed in C2: =SQRT((A2-$A$1)^2+(B2-$B$1)^2)

#3) Next I multiply C2 by a number so that distance is measured correctly per unit type for this game. (I hide this column). Therefore, cell D3 has the following formula: =C2*20

#4) Now my problem, cell E3 - Correctly displaying time. I have the following formula in E3: =TIME(0,D2,0)

This works great!! That is until the time goes beyond 24 hours, then it rolls over. I've tried right clicking and setting the custom format of E3 to [hh]:mm, it still rolls over. Iíve also tried setting formatting to d:[hh]:mm, if I do this then the output time displayed is incorrect.

Ideally I would love a simple fix so that the time will roll over and show days. (I would like a display similar to d:hh:mm).

Iíve done a fair bit of research online and either I'm missing something simple or this is more complex than I thought. And not to make this any more confusing, but I would love whatever I accomplish here to be useable in compatibility mode ...

View Replies!   View Related
Time Mask For Thousands Of Hours
Basically I want to be able to type a number and have a colon inserted two spaces to the left. So 123 would become 1:23, 1234 would be 12:34 and 12345 would be 123:45.

I'm working with anywhere from one to several thousand hours in a spreadsheet. I'd like to be able to use a time mask like this:

View Replies!   View Related
Conditional Formatting Using Hours/time
I'm trying to use conditional formatting to highlight phone calls that came in between certain hours.

The call times are in the custom format h:mm, although it could easily be changed to an Excel time format. I'm using Excel 2002. I want the cell to have a different fill color if it falls within particular time frames.

Example:

If the call came in between 22:00 and 23:59 color is light green.
If the call came in between 23:59 and 08:00 the color is yellow.

Example spreadsheet is attached.

View Replies!   View Related
Converting Military Time To Hours
Looking for a formula that will convert military time into hours and total the hours in each row going accross up to 31 days. Only problem is the word 'OFF' is included on various days in each row.

View Replies!   View Related
Converting Time Period In Hours
How do I convert 7.30 hours into 7.21 (ie 7 hours 21min.) Note I do not wish
to use the standard hour:minute formatting.

View Replies!   View Related
Convert Time Range To Total Minutes
I have the following time ranges that need to be converted into total minutes. Examples (from easiest to most difficult)...

The items below are in column A (each range is the content of just one cell):

00:00-13:00
06:30-13:15
13:30-13:15 (this spans two days, so it's actually 23H and 45M)
23:30-03:00 & 16:00-22:10 (only the first time range matters; the second can be omitted)

So for 00:00-13:00, we have 13H and 0M = 13 * 60 = 780. And for 23:30-03:00, we have 3H and 30M = 3.5 * 60 = 210. But how do I automate this process with the text entries above (and hundreds more that are imported in this format).

View Replies!   View Related
Calculate Date/time Format Over 24 Hours
I need to calculate the difference between two cells that are formated like this: 11/10/2006 13:00. Simple subtract only works when the date hasn't changed (the subtract formula seems to focus on the time and ignore the date).

I found a formula that will work, but the results are in regular numbers, I need the hh:mm format and if I change the format of the result column, the time displayed is wrong.

The formula is=((B2-A2)*1440)/60)

If my result is 122.6 in regular numbers, when I convert to hh:mm I get 14:00.

Is there a better way to do this? Also, is there a way to eliminate weekend & holiday hours from the calculation? This is not as important as getting the results to calculate correctly in the hh:mm format.


View Replies!   View Related
Calculate Hours Worked After Specific Time
i have a timesheet that we are trying to use. the problem is the column that says shift diff. if an employee works after 6:30pm for 1and 1/2hr, he is entitled to shift hours. shift hours is between 6m and 8am.

As long as he works after 6.30pm but works for at least one and a half hour, he will get the shift.

if work, 9am to 7:30pm, and have break between 2-3pm, should have 1.5hrs shift and 9.5hrs total
if work, 7:45pm-9:45pm, and have break between 8:30-9pm, total hrs work is 1.5 and shift hrs s/b 1.5hrs
if work 3pm to 12am and have break between 7-8pm, total hrs work is 8 and shift hrs s/b 5hrs

View Replies!   View Related
Subtracting Business Hours From A Data And Time Value
I am trying to subtract 12 business hours from a date/time stamp. It has to be during working hours however which are 8AM - 5PM, Monday-Friday.

For example if my value is 6/23/2008 9AM

Subtracting 12 business hours would give me 6/19/2008 3PM.


View Replies!   View Related
Calculating Hours With Midnight As A Start Time
Iím working on a timesheet and I need to separate the hours worked that are before 6 am from all the others. For example if someone works 1:00 am to 8:00 am I need a cell to populated with 5 representing the hours worked before 6 am. The formula below works fine except when the start time is 12:00 am. I am also having trouble if the start time is before midnight like in a 11pm to 5 am shift.

=(IF(AND(S3<=$AG$97,S3>=$AG$73),(($AG$97-S3))*24,0))

S3 is the start time

AG97 is 6:00 am

AG73 is 12:00 am

View Replies!   View Related
Time/date Tracking Only During Business Hours
i work dispatch at a company and we're trying to track how long it takes for a technician to arrive on-site. my worksheet currently includes the date of call-in (E1) , time of call-in (F1), Service Date (G1), Service Time (H1), among many other field. the easy solution is to use the formula (G1+H1)-(E1+F1), and use the answer as the total amount of time, but the problem is if a customer call in at 3:00 PM and we service them at 9:00 AM the following day, it looks like it took 18 hours to arrive on-site, when in reality, since we close at 5:00 PM and open at 9:00 AM, we only took 2 hours to arrive on-site. is there any formula i can write to account for non-business hours and weekends, or is this a bit above what i should expect from excel?

View Replies!   View Related
Calculating Time To Track Work Hours
I can't figure out how to do a proper formula for calculating time. For instance I have one column that says "Time In", the next is "Time Out" and the other is "Lunch Time". My calculation needs to be this:

Find the total hours between the Time In and Time Out and then Minus the lunch time to get total hours worked for the day. The only way I can get this to work is using 24 Hour time format. Is there another way?

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved