Formula that will change seconds to hours and mins, example 270 mins would show up as 4 hours 30 mins, not 4.5 hours. My second thing is how can I double the times in the same formula.
If a person does a full day I want the formula to calculate how many hours minus 30 mins for lunch i.e. 7:30 - 4:00 = 8 hours. I have done this and it works fine, the problem comes when a person does a half day i.e. 7:30 - 11:30, this will read 4.5 hours. If this is the case I don't want the 30mins deducted so it should be 4.0 hours worked.
I have high lighted a column in the work sheet (yellow) whereby the user puts an 'x' in the appropriate cell if they are working a half day.
The probem is I cannot incorperate the two formulas in one cell (the Actual hours cell) I have the one formula in the actual hours cell and the other bolted on the end thus giving two different readings.
The below formulae allows me to see the difference between two dates and only returns the difference in working hours ie : Difference between 02/02/2010 08:00 & 03/02/2010 08:00 is 16 Hours 0 Minutes
=(INT(A3)-INT(C6))+MAX(MOD(A3,1)-MAX(MOD(C6,1)))
The following displays it in the Hrs and Mins format
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.
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.
I have a data feed that gives a column of cells formated mm/dd/yy hh:mm These are call entries, what i want to do is count all the occurances each hour, so count all the entries say between 9am and 10am. However the column is very long and holds data for many weeks so i want to be able to ignore the date part of the cell.
I have an entire column with numbers such as 48, 95, 30 etc representing hours. how do i format cell so that these numbers will show, 2days, 3days-23hrs-45mins, 1day-6hrs
Thought it would be easy enough to just right click and format the cell but doesn't seem to have the option *shrugs*.
I was messing around with a simple countdown timer and need a little help converting a decimal into a format of #days, #hours, #minutes and #seconds.
Cell A1 has target to countdown to 4/14/2010 12:00:00 AM Cell A2 contains =NOW() Cell A3 contains =A1-A2 with the result being 69.4021441 with the amount of decimal places varying depending on when it is refreshed.
I'd Like the result in A3 to read something like "There are 69 days, XX hours, XX minutes and XX seconds until event"
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?
I am trying to add duration time for song files. 4 minutes and 32 seconds + 3 minutes 45 seconds, etc. etc. to get total hours and then to total to days. The range of cells is over 5,000 and I am trying to find out how long a playlist will play.
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.
Is there a way to have it show the number of days, hours, minutes and seconds? So it will say 45:1:23:42? (45 days, 1 hour, etc...) Or something along these lines?
i have =TIME(LEFT(F17,FIND("h",F17)-1),RIGHT(F17,2),0). which gives a time in the format 9:30PM, or 10:30AM etc. How would i change the formula to give me a time minus 15 mins. Eg, if the above formula gave me 8:45PM, i would want it to show 8:30PM
I’m using live data and was wondering if it’s it possible to trigger B1 to change its value from ‘no’ to ‘yes’ 20 seconds after cell A1 changes its value from 0 to 1?
I have a spread sheet with a colum showing average time to complete a task. This is currently shown as Days:Hours:Minutes:Seconds (4:19:33:19). I meed it to be shown purely as minutes, or at least as hours and minutes.
I have got a formula that can separate day shift hours from night hours, in this case night begins at 7pm to 7am, however the problem is after 12am we get into negative numbers, what formula would fix this and can be combined with the formulas below?
E10 = 19:00 or start of night time hours B3 = start time C3 = end time D3 = day hours workeds =24*IF(E10