Minutes

Hi there,

I have a timesheet which works out working hours e.g. Time in 8:52 time out 
12.00 the answer is 3.13 so I then manually calculate the minute bit 0.13*60 
which equals 8 minutes. Is there any clever way to calculate this to show 
hours and minutes?

TIA
-- 
Bobby
0
Bobby (41)
8/10/2006 2:06:02 PM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
513 Views

Similar Articles

[PageSpeed] 42

a2 =3D 8:52
b2 =3D 12:00
c2 =3D b2-a2

Steve

On Thu, 10 Aug 2006 15:06:02 +0100, Bobby  =

<Bobby@discussions.microsoft.com> wrote:

> Hi there,
>
> I have a timesheet which works out working hours e.g. Time in 8:52 tim=
e  =

> out
> 12.00 the answer is 3.13 so I then manually calculate the minute bit  =

> 0.13*60
> which equals 8 minutes. Is there any clever way to calculate this to s=
how
> hours and minutes?
>
> TIA



-- =

Steve (3)
0
sj_walton (248)
8/10/2006 2:16:32 PM
If you have all three cells set with the format of hh:mm in custo
format then when you do your subtraction the result will show the tim
in hours and minutes. Also you need to make sure that you use the 
symbol to seperate your hours and minutes.

Hope that helps

Regards

Car

--
mr_teache
-----------------------------------------------------------------------
mr_teacher's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3435
View this thread: http://www.excelforum.com/showthread.php?threadid=57035

0
8/10/2006 3:17:22 PM
Hi Steve and Carl, 
I tried your suggestions on my spreadsheet and got strange answers so tried 
them on a blank worksheet and it worked fine so thank you!
I think the problem I have is that the cells have been formatted differently 
to work with the formulae in them. The worksheet is a template from microsoft 
and the workings are listed below.
Help!

Cell b11=time in 
Format time format
Displayed 8:52

Cell b12= time out
Format time format
Display 12:00

Cell C12= 
Formula 
=IF((OR(B12="",B11="")),0,IF((B12<B11),((B12-B11)*24)+24,(B12-B11)*24))
format = 0.00_);[Red](0.00)
Display 3.13 

Cell b14=time in pm
Format time format
Display 12:30

Cell b15=  time out pm
Format time format
Display 17:14
Cell C15 = 
Formula 
=IF((OR(B15="",B14="")),0,IF((B15<B14),((B15-B14)*24)+24,(B15-B14)*24))
Display 4.73

Cell B16 = Total time worked
Formula =IF(OR(ISTEXT(C12),ISTEXT(C15)),"Error in C12 or C15",(C12+C15))
Format = general
Display = 7.87

It would be so great if someone could find me a solution
-- 
Bobby

0
Bobby (41)
8/10/2006 9:04:02 PM
Hi,

Not an expert on macros, but using just the formulas I have got this t
work if this is what you want - it has only changed the last part o
your macro as it stood. I have just added my formula in here so don'
know if it will work or not. 

If this is not working then you could delete the last section of you
macro and in cell B16 put the formula

=LEFT((C12+C15),LEN(C12)-3)&"hour
"&ROUND(((C12+C15)-(LEFT((C12+C15),LEN(C12)-3)))*60,0)&"mins"

Hope this works

Regards

Carl

Cell b11=time in
Format time format
Displayed 8:52

Cell b12= time out
Format time format
Display 12:00

Cell C12=
Formula
=IF((OR(B12="",B11="")),0,IF((B12<B11),((B12-B11)*24)+24,(B12-B11)*24))
format = 0.00_);[Red](0.00)
Display 3.13

Cell b14=time in pm
Format time format
Display 12:30

Cell b15=  time out pm
Format time format
Display 17:14
Cell C15 =
Formula
=IF((OR(B15="",B14="")),0,IF((B15<B14),((B15-B14)*24)+24,(B15-B14)*24))
Display 4.73

Cell B16 = Total time worked
Formula =IF(OR(ISTEXT(C12),ISTEXT(C15)),"Error in C12 o
C15",LEFT((C12+C15),LEN(C12)-3)&"hour
"&ROUND(((C12+C15)-(LEFT((C12+C15),LEN(C12)-3)))*60,0)&"mins")
Format = general
Display = 7.8

--
mr_teache
-----------------------------------------------------------------------
mr_teacher's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3435
View this thread: http://www.excelforum.com/showthread.php?threadid=57035

0
8/11/2006 11:59:38 AM
This looks as though you actually wanted decimal hours anyway.

time in/out - lunch - time in/out

All input (time) is entered hh:mm
all output (time in hours)

Presumably you wanted a time (in hours) to enable pay caclualtion etc et=
c

So in the end those forumula look correct

If you want to see that decimal hours as hh:mm
then add an extra colum
b17 =3D b16/24
format custom hh:mm

Steve

On Thu, 10 Aug 2006 22:04:02 +0100, Bobby  =

<Bobby@discussions.microsoft.com> wrote:

> Hi Steve and Carl,
> I tried your suggestions on my spreadsheet and got strange answers so =
 =

> tried
> them on a blank worksheet and it worked fine so thank you!
> I think the problem I have is that the cells have been formatted  =

> differently
> to work with the formulae in them. The worksheet is a template from  =

> microsoft
> and the workings are listed below.
> Help!
>
> Cell b11=3Dtime in
> Format time format
> Displayed 8:52
>
> Cell b12=3D time out
> Format time format
> Display 12:00
>
> Cell C12=3D
> Formula
> =3DIF((OR(B12=3D"",B11=3D"")),0,IF((B12<B11),((B12-B11)*24)+24,(B12-B1=
1)*24))
> format =3D 0.00_);[Red](0.00)
> Display 3.13
>
> Cell b14=3Dtime in pm
> Format time format
> Display 12:30
>
> Cell b15=3D  time out pm
> Format time format
> Display 17:14
> Cell C15 =3D
> Formula
> =3DIF((OR(B15=3D"",B14=3D"")),0,IF((B15<B14),((B15-B14)*24)+24,(B15-B1=
4)*24))
> Display 4.73
>
> Cell B16 =3D Total time worked
> Formula =3DIF(OR(ISTEXT(C12),ISTEXT(C15)),"Error in C12 or C15",(C12+C=
15))
> Format =3D general
> Display =3D 7.87
>
> It would be so great if someone could find me a solution



-- =

Steve (3)
0
sj_walton (248)
8/11/2006 3:33:53 PM
Reply:

Similar Artilces:

Convert minutes to hours and minutes
I have a formula that converts whole numbers with a decimal to minutes. Example 1.5 =110 minutes. I am trying to convert the total minutes back to a whole number with a decimal in minutes. For example 110 minutes = 1.5. If I divide the minutes by 60 I get 1.83 where I want to get back to the 1.5. Does anyone know the formula to do this?? Hi Sally maybe I'm missing smething but how did you convert 1.5 to 110 minutes (I had suspected 90 minutes??) So the value 1.83.. (1,83 hours) is correct for 110 minutes (if converted to decimals) Frank Sally IC wrote: > I have a formula that c...

Converting Minutes:Seconds to Minutes
I could download my telephone bill online. When I view the bill online The Duration of Call column which is listed in minutes appears as (for example) 5:00 ..............(for 5 minutes) 16:00.............(for 16 minutes) 21:00.............(for 21 minutes) 29:00.............(for 29 minutes) 32:00.............(for 32 minutes) When I copy or download the bill this column appears as (Same example) 5:00................(for 5 minutes) 16:00.............(for 16 minutes) 29:00:00.............(for 29 minutes) 32:00:00.............(for 32 minutes) I would like to be able to do a total for the number o...

Converting Minutes to Seconds
I have a list of times formated in minutes and hundredths of minutes for example 7360.50 and 8622.42. I need to be able to covert thes numbers to seconds. I am sure this is an easy formula, I just don' have the time to think it through. Thanks. PS. Just to clarify 7360.50 is the same as 2:40:30 and 8622.42 i 23:42:25 -- Message posted from http://www.ExcelForum.com I think you got it wrong, haw can 7360.50 minutes be only 2:40:30 You need to use [hh]:mm:ss as format to get all hours, otherwise get up to 24 hours However if you want it in seconds just multiply with 60 =7360.50*6...

Word 2008 keeps crashing after a document is open, a few minutes after typing anything
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel For the last two weeks Word 2008 has been freezing after a open my document and start typing. The documents were created in word. I removed the &quot;Normal.dotm&quot; file as some suggested for similar issue. But it does not solve the problem. I reinstalled office with all updates again and nothing. PLEASE HELP! I'm writing a dissertation! <br><br>Here is the &quot;problem report for word&quot;. I'm sorry but is long. <br><br>Date/Time: 2010-05-10 21:20:20 -...

Minutes into Hours and Minutes
I have a spreadsheet which I use to keep the times of some events. I get these numbers in minutes and need to convert them into HH:MM format. Just divide your minutes value by 86400 (the number of minutes in a day)... =A1/86400 and format the cell like this... [hh]:mm (the square brackets allow the hours to be more than 24). -- Rick (MVP - Excel) "david d" <davidd@discussions.microsoft.com> wrote in message news:E2E72CBB-BE21-4239-9C61-CEF998849234@microsoft.com... >I have a spreadsheet which I use to keep the times of some events. I get > these numbers in m...

getting Outlook "popup" window every few minutes.....
Hello, When using Outlook 2002 I get a box popping up on my screen every few minutes saying: "Unable to update public free/busy data. Operation failed" I recently set up a second data file and successfully created a rule to divert emails from a domain name I have to that data file and loaded a bunch of appointments into the calendar of that data file. Is this popup to do with that? Thanks Try starting Outlook once with the /CleanFreeBusy switch. If you need help using Outlook command line switches, look here: http://support.microsoft.com/default.aspx?scid=kb;en-us;296192 -- J...

To convert minute into hour and minute
How to convert 250 minutes into 4 hours and 10 minutes? Which the formula The result of one determined function is 250. I would like from this value to convert it into 4 hours and 10 minutes (4:10). How I make The formula counts the numbers of times, during one week, that it has lessons of Mathematics. The result is 5 lessons, being that 1 lesson of mathematics corresponds the 50 minutes. Then I multiply 5*50=250. What I want is that value 250 either converted into 4h and 10min or 4:10 hours Thank Enter the 50 minutes as 00:50, multiply by 5 = 4:10 To get the time as decimal 00:50*5*24 = ...

Minutes to hrs calcultion
I have a form where you enter minutes but the calculation needs to give the result in Hr:min. I've tried A2=180 A3=20=SUM(A2,A3)/60 The result being 3.3 but I need it to show 3hrs 20 min Sounds daft I know! Mark You could try the following: =INT(A2/60)&" Hrs "& (MOD(A2,60))&" mins" this will look at A2 - if it has 200 the formula will translate that to 3 Hrs 20 mins edvwvw Mark Lewis wrote: >I have a form where you enter minutes but the calculation needs to give the >result in Hr:min. I've tried > A2=180 A3=20=SUM(A2,A3)/60 ...

Outlook 2007 take ten minutes to open
My Outlook started to take ten minutes to open after boot. Tried all published fixes? Was working fine last week, Try opening Outlook using the safe switch, ok then? "Damaker" <eliot@nyc.rr.com> wrote in message news:Ro2dnRQU07_-U7DXnZ2dnUVZ_oqdnZ2d@giganews.com... > My Outlook started to take ten minutes to open after boot. Tried all > published fixes? Was working fine last week, > ...

Converting Hours and Minutes to Just Minutes
Does anyone have a formula for converting for example 1 hr 20 m to 8 minutes? Thanks for your help. Karen -- cny ----------------------------------------------------------------------- cny2's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2567 View this thread: http://www.excelforum.com/showthread.php?threadid=49840 The answer depends on the format of your data, and perhaps how the data is stored. For instance, is it in the format of your example, 1 hr 20 m, contained as text in a single cell? If yes then you'll need to parse the 1 and the 20 out s...

Summing Hours and Minutes
Hello, I have a TIME ...I then subtract from it another TIME. The first is formatted like 4:30 PM the second is formatted like 10/5 5:00 PM. I then get the answer and I format it using hh:mm so my answer may be 5:44 ...meaning 5 hours 44 minutes.... I try to take all the answers and get a sum and I get ##### I make the column wider...but its never wide enough so I suppose something is wrong with my premise here. Any help is greatly appreciated. Ken Hi, Try the following formula in another cell, =INT(B10*24)&" : "&IF(LEN(ROUND(MOD(B10*24,1)*60,2))=1,"0"&RO...

Send 900 Billion valid Emails in Just 5 Minutes Only!
...

convert hours and minutes to minutes
I need to convert hours and minutes 1:45 (One Hour and 45 Minutes) into total minutes... Any ideas Use a formula like the following: =A1*24*60 Format the cell as General or Number. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "idaho" <idaho@discussions.microsoft.com> wrote in message news:80B42899-7817-4D6B-A1BF-1ACBF493C53F@microsoft.com... >I need to convert hours and minutes 1:45 (One Hour and 45 >Minutes) into total > minutes... Any ideas If it's just formatting you want. Try this Custom Form...

Outlook won't open (takes 5 minutes)
I have a user here at work running XP that says sometimes when she tries to open Outlook, nothing comes up. She will try a couple of times and a few minutes later Outlook will open several windows. (1 for everytime she tried to open it) At other times it just opens right up the first time correctly. I looked at the task manager and the times it does not seem to come up, it will have a Outlook.exe file running. I ran the repair and fix and it still does it. Please help. I wanted to add my email address in case anybody had any ideas what to do. Thanks I have this problem on seveal Win ...

How to add hours and minutes?
I want to calculate the following start time finish time meal break total worked Formula would be something like finish time - start time - meal break = total That part I can do Problem And then add the total time worked so that I would get total hours and minutes worked over a two week period. The end result would be something on the order of 48:20 where the 48 is the hours and the 20 is the minutes. Can anyone help? dave Hi Dave, Calculating with time is well explained here: http://www.cpearson.com/excel/datetime.htm#AddingTimes -- Kind regards, Niek Otten Microsoft MVP - Excel ...

Excel is starting over a minute
Hi all, Sorry if this subject was covered already, but I really need help, and cannot find the answer. I am using Excel 2003 Clean start (with blank sheet) take over a minute, it is behaving like it hanging, no response. The same is with opening a XLS file eg. from e-mail or dblclicking on an XLS file. I am not using any anti-viral software. I checked, it is not processing any files in that time. I don't have anything in XLSTART folder. Those are potential reasons I found on the net and I checked them. I would appreciate any ideas. Regards Pawel ...

rolling up minutes to hours
setting up a meeting where tasks run 10-30 mins each. How do you make Project add that time up to the overall time in that section? (ex. 3 20-min tasks = 1 hour) we don't want percentages or decimals of the day, just hours thanks -- TC Hi TC, I'm not sure I understand completely what your question is. If you have the meeting set as a summary task and each of the tasks making up that meeting indented as subtasks under the meeting summary task, the summary task duration should show the total duration of the span of the tasks. Have you linked each of the subtas...

MSExchangeIS 1021 error repeating every minute
I get about 5 of these errors popping up every minute on my Exchange server. When I open it, they all say: DOMAIN/user was unable to connect as /o=Domain/ou=SAF Administrative Group/cn=Recipients/cn=epage. Error 0x3f2. I have been through the following articles already and no luck: http://support.microsoft.com/default.aspx?scid=kb;en-us;290647 http://support.microsoft.com/default.aspx?scid=kb;en-us;314494 http://support.microsoft.com/default.aspx?scid=kb;en-us;810907 http://support.microsoft.com/default.aspx?scid=kb;en-us;832215 http://support.microsoft.com/default.aspx?scid=kb;en-us;83464...

How do you subtract time? (ie 03:15 am minus 5 minutes)
I am setting up a spreadsheet to figure on start time from the end time to start time. I need to subtract time from the previous cell to the next cell. Hi, Use the article 214094 from support.microsoft.com Also make sure that you have the cell formatting to Time 37:30:55 Manish "Dennis" wrote: > I am setting up a spreadsheet to figure on start time from the end time to > start time. I need to subtract time from the previous cell to the next cell. One way: XL stores times as fractional days, so all you need to do is subtract, and format the resulting cell as a time: ...

Every 2 minutes
Hi, if I have a table: DateTimeField SomeValueField '17/11/2009 23:50:35' 10 '17/11/2009 23:51:30' 20 '17/11/2009 23:51:35' 21 '17/11/2009 23:52:10' 1 .... .... I need to do a group by showing the sum at every 2 minutes, so it'd be: The column returned must show: Minute = 52 - Sum: 10+20+21=51 Minute = 54 - Sum: 1 .... .... Using MS SQL 2k Thanks > I need to do a group by showing the sum at every 2 minutes, so it'd be: > > The ...

Convert hours:minutes to minutes
I have a calculated field in a query that returns the difference between two times with a time format of h:nn; for example, one hour and thirty minutes is displayed as 1:30. I need the value of 1:30 displayed in minutes, so I need to see the value as 90 minutes. If I use the "n" format for minutes it only returns the minutes without the hours so in the case of 1:30 it only returns 30 and not 90. How do I format or calculate the field to return the value in minutes so that 1:30=90, 2:45=165, 0:33=33? "Scott" <Scott@discussions.microsoft.com> wrote in message...

Display subject lines that carry [error:] on a web page, which refreshes every x minutes automatically #2
Hello, I don't know if this is possible or not. But it is worth giving a try. I have an application that sends notification emails saying that there is an error that has occured with the application and this email contains "error:" as a constant term in the subject line, and they are all from h...@application.com. Now, What I want to do is my outlook macro or code should periodically refresh the outlook inbox look for subject line that has "error" and verify it is from h...@application.com and display this error subject line on a web page. This should happen every ...

Formula for minutes to days:hours:minutes
I have a report out of another system that shows ventilator time in minutes and the end user would like to see it in days:hours:minutes. It's difficult to put your head around how long 7171 minutes is. Thank You Divide by 1440 and use a custom format [h]:mm will get you the time in hours -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "QueenCutieT" <QueenCutieT@discussions.microsoft.com> wrote in message news:2B91EB3B-EF8D-4E7E-9B10-2D0139A8E938@microsoft.com... >I have a report out of...

Converting Hours an minutes just into minutes
Is thier a formula to use to change hous and minutes, just into minutes ex: 01:30 (1 hour and 30 minutes) into 90 minutes. And how do you format the cells. Thank you Hi Excel stores times as fractions of a day. Therefore to convert to hours, multiply by 24 (result 1.5) To convert to minutes multiply by 1440 (24 * 60 ) (result 90) Format cell as General. -- Regards Roger Govier "Six Sigma Blackbelt" <SixSigmaBlackbelt@discussions.microsoft.com> wrote in message news:436BAFDB-03C5-451F-B390-783AA6386B27@microsoft.com... > Is thier a formula to use to change hous a...

hours and minutes storing past midnight
i have a query that sums up all hours and stores this as a main figure lets for this question say 78:34 to store this in an access table i need to use a text type but when i try and do 78:34-30:00 to give me 48:34 it gives me a message saying invalid data type, now im guessin its due to it being saved as a string but am an unable to find the correct data type in a table to store it On Fri, 7 Dec 2007 11:19:00 -0800, Rivers <Rivers@discussions.microsoft.com> wrote: >i have a query that sums up all hours and stores this as a main figure lets >for this question say > &...