Formula Change Seconds To Hours And Mins

Feb 10, 2010

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.

View 9 Replies


ADVERTISEMENT

Formula To Calculate How Many Hours Minus 30 Mins For Lunch

Jan 12, 2009

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.

View 14 Replies View Related

Difference Between Dates And Times In Days , Hours Mins ( Working Hours )

Mar 2, 2010

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

=TEXT(B15,"[h]")&" Hour"&IF(OR(TEXT(B15,"[h]")+0=0,TEXT(B15,"[h]")+0>1),"s "," ")&MINUTE(B15)&" Minute"&IF(MINUTE(B15)1,"s ",""))

View 9 Replies View Related

Convert Hours, Minutes And Seconds Into Total Seconds

Jun 28, 2008

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 5 Replies View Related

Convert Second To Hours And Mins (over 24 Hours)

Apr 21, 2009

i need to convert second to Hours and mins and can do so using:

View 3 Replies View Related

Convert Seconds Into Hours:Minutes:Seconds

Jun 18, 2008

Is there an easy way of converting seconds from using a time into hours, minutes, seconds

View 9 Replies View Related

Hours:minutes:seconds Convert To Seconds

Jul 13, 2006

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 9 Replies View Related

Convert Seconds Into Hours, Minutes & Seconds

Dec 7, 2007

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 7 Replies View Related

Countif Of Hours And Mins Not Dates

Mar 17, 2009

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.

For example

3/17/09 9:58
3/18/09 10:58
3/18/09 8:58
3/19/09 8:58
3/19/09 9:58
3/19/09 9:58

So the entries between 9 and 10 are = 3

Ideally i do not want to do text to colums and split out the parts. i would like to leave the source data alone.

View 7 Replies View Related

How To Convert Numbers Into Day / Hours / Mins

Nov 23, 2012

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*.

View 2 Replies View Related

Convert Time Decimals To Hours Mins

Jan 14, 2009

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 9 Replies View Related

Convert Decimal To Days/hours/mins/secs

Feb 3, 2010

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"

View 10 Replies View Related

Numberv To Time Formula: Change 66 To Read 00:01:06 I.e. 1 Min 6 Seconds

Nov 5, 2009

How mould i change 66 to read 00:01:06 i.e. 1 min 6 seconds? i know i'ds use mod and int but not sure how.

View 2 Replies View Related

Convert Hours To Minutes And Seconds

Jan 8, 2007

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 10 Replies View Related

UDF For Converting Seconds To Hours And Minutes

Jun 6, 2014

[URL].....and the answer worked really well; up until a point of

Total Seconds32767

Time From Seconds09 hrs 06 minutes(s) and 07 second(s)

any higher than this value brings a #value error.

View 2 Replies View Related

Days, Hours, Minutes And Seconds

Sep 23, 2008

Does any know how I can convert 29.18:20:18 to total minutes?

View 7 Replies View Related

Adding Seconds And Minutes To Get Hours And Days

Nov 13, 2006

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.

View 9 Replies View Related

Convert Decimal Minutes & Seconds To Days & Hours

Dec 2, 2006

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 5 Replies View Related

Format Time To Show Hours, Minutes & Seconds

Jul 6, 2007

is there a way in VB to make it fast, and tidy, clear the contents of all unlocked cells on one sheet ?

View 2 Replies View Related

Time Difference In Hours / Minutes And Seconds Between 2 Date / Timestamps

Aug 22, 2012

I have

Code:
Start date 21/08/2012 23:21:30
End Date 24/08/2012 22:21:45

is there a formula to get the time difference in Hours for example in this case its 72:01:15 72 Hours, 1 minute & 15 seconds

View 2 Replies View Related

Cell Formated To Show Days, Hours, Minutes, Seconds

Apr 1, 2009

I have the foollowing equation in a cell:

=NETWORKDAYS(A2,A12)+G12

My answer is 1081:23:42.

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?

View 9 Replies View Related

Formula To Give A Time Minus 15 Mins

Aug 1, 2008

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

View 4 Replies View Related

Converting Minutes, Seconds And Tenths Of Seconds Into Seconds

Jan 18, 2010

I want to change 1 minute 24 seconds and 5 tenths into a seconds number. So basically it would come out as 84.50 seconds.

View 4 Replies View Related

Delay A Change For 20 Seconds

Sep 23, 2007

Im using live data and was wondering if its 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?

View 9 Replies View Related

Change Decimal Minutes Into Seconds

Oct 10, 2012

I am looking for a simple formula to change decimal minutes into seconds for example 0.12 = 7 seconds

View 3 Replies View Related

Convert Days:Hours:Minutes:seconds To Minutes?

May 25, 2011

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.

View 4 Replies View Related

Looking For A Formula That Can Separate Day Shift Hours From Night Hours?

Aug 6, 2013

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

View 1 Replies View Related

Convert Decimal Minutes & Seconds To Total Seconds

Sep 23, 2007

How can I Convert Decimal Minutes & Seconds To Total Seconds?

Here is the sample

View 4 Replies View Related

Converting Seconds Or Minutes:Seconds To A Number

Jun 21, 2009

How would I go about converting - for example:

0:22 (formated general) to a number = 22

or

1:30 (formated general) to a number = 90

View 9 Replies View Related

Converting Seconds Into Minutes And Seconds?

Jan 29, 2013

I have been trying for the last two weeks to convert a list of seconds into minutes and seconds, but just can not do it?

Below is an example of my raw data, that I need to convert into minutes and seconds:

120000

120000

120000

2880000

480000

[code].....

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved