Change Time in a Cell from [hh]:mm to decimal value ie 16:45 - 16.

Here lies the problem.  I'm cheap, I want to be paid for every minute that I 
work,  I do the time cards for work and I'm using Excel to perform the 
functions.  I have no problem with adding and subtracting the times in single 
cells or totalling all of the time.  If I come up with a person having 47 
hours and 13 minutes how do I get Excel to convert 47:13 to 47.22 so that I 
can then multiply the 40 by hourly rate and the 7.22 by 1.5 the hourly rate ? 
-- 
Thank You, God Speed, and Semper Fidelis
0
Lewis (18)
11/12/2005 5:27:02 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
445 Views

Similar Articles

[PageSpeed] 27

Hi!

This all depends on how you have things setup.

To convert 47:13 to 47.22 just multiple 47:13*24.

If 47:13 is the result of a formula:

=SUM(A1:A7)

Then:

=SUM(A1:A7)*24 returns: 47.21667

So, if you want 47.22 then you'll have to round:

=ROUND(SUM(A1:A7)*24,2)

Again, this all depends on your setup!!!!

Assume that formula is in A10.

For the regular hours (<=40)

=MIN(A10,40)*regular_rate

For OT (>40)

=MAX(A10-40,0)*OT_rate

Biff

"Lewis" <Lewis@discussions.microsoft.com> wrote in message 
news:106F7289-C6B7-456D-B4CC-1283D2B5DA64@microsoft.com...
> Here lies the problem.  I'm cheap, I want to be paid for every minute that 
> I
> work,  I do the time cards for work and I'm using Excel to perform the
> functions.  I have no problem with adding and subtracting the times in 
> single
> cells or totalling all of the time.  If I come up with a person having 47
> hours and 13 minutes how do I get Excel to convert 47:13 to 47.22 so that 
> I
> can then multiply the 40 by hourly rate and the 7.22 by 1.5 the hourly 
> rate ?
> -- 
> Thank You, God Speed, and Semper Fidelis 


0
biffinpitt (3172)
11/12/2005 7:18:12 AM
this is the solution I found in one of these newsgsroups

for e.g. a1
47:13:00

now in B1 type
=a1*24
 and then FORMAT B1 as GENERAL (or some numeric format) -Not as time

you will get
47.21666667


"Lewis" <Lewis@discussions.microsoft.com> wrote in message
news:106F7289-C6B7-456D-B4CC-1283D2B5DA64@microsoft.com...
> Here lies the problem.  I'm cheap, I want to be paid for every minute that
I
> work,  I do the time cards for work and I'm using Excel to perform the
> functions.  I have no problem with adding and subtracting the times in
single
> cells or totalling all of the time.  If I come up with a person having 47
> hours and 13 minutes how do I get Excel to convert 47:13 to 47.22 so that
I
> can then multiply the 40 by hourly rate and the 7.22 by 1.5 the hourly
rate ?
> --
> Thank You, God Speed, and Semper Fidelis


0
R
11/12/2005 9:34:31 AM
Reply:

Similar Artilces:

Excel VB-Copy formula down until adjacent cell (left) is blank?
Here is exactly what I am trying to do through VB in Excel: Weekly data pull fills colums A:G. Row count is always different. I am modifying the data pull through VB, and I have a VLOOKUP formula in cell H2. What I want VB to do is copy that formula down column H to the last row (with data) each week. I guess I want it to be dynamic so that as rows decrease/increase the formula is only copied down to the final row/record. I know someone out of this smart group will know how to do this! Thanks in advance! Tony (pseudo code) in a macro .... dim lngLastrow as long dim rngTarget...

How to correctly change security?
There are some computers in domain on which the user permissions are set as Power User, but I want to remove them and let computer to bring permissions and policies from domain controller server. I removed Power User from computer in Control Panel, but are there places where I must make changes? Is this enough? On Apr 14, 5:09=A0pm, girts <gi...@discussions.microsoft.com> wrote: > There are some computers in domain on which the user permissions are set = as > Power User, but I want to remove them and let computer to bring permissio= ns > and policies from domain c...

Linking Time-varying charts
Hi all, Here's my problem. I've got a set of Excel files with monthly data which is updated every month. So in January I only have Jan data. In February I have Jan and Feb data, and so on. The chart I have for this data always displays a full 18 months data from October 2003 to March 2005, and as more data is added, the line on the chart snakes its way from left to right. These files are submitted to me from various sources. As a central data collector, I now want to link these Excel files into a single Excel file which will display all the charts in one place for convenience. Ho...

Sorting numbers with multiple decimal points?
Hi - I'm sure this is covered in some docs somewhere, but it's hard to know what to search for. I have a spreadsheet (using Excel 2003 SP1) in which I have a number of rows that have an "ID" field taking the format "X.X.X.X" where X is a number from 0 to 100. Like this: 1.1.0.0 blah blah rest of row 1.1.2.4 blah blah rest of row 1.1.3.0 blah blah rest of row 1.0.0.0 blah blah rest of row 1.1.3.2 blah blah rest of row 1.1.3.1 blah blah rest of row 1.1.1.1 blah blah rest of row 1.1.3.0 blah blah rest of row 1.1.1.3 blah blah rest of row 1.1.2.5 blah blah rest of ...

changing a custom toolbar
I have a legacy access database that i'm now working on in Access 2007. I have a custom toolbar set up in there (at least that is what A2007 shows it as on the Add-Ins tab of the ribbon). I need to edit that custom toolbar (menu). How do I do that? Karen Hey Karen, I have spent a lot of time trying to find examples to be able to edit a menubar from a previous version in Access 2007. Finally, I have come up with the following that works! I hope this helps: --- start code --- Function TweakToolbar() Dim cbr As CommandBar Dim cbrctl As CommandBarControl Dim cbrbutt As C...

Read Online Statement -- All the Time in Money 2006?
I just upgraded to Money 2006 from the '04 edition. It appears that it always wants me to "Read Online Statement" for all of my investment accounts, even though there's been no actual transactions in the funds. I think all that's changed is the price of the fund or stock for the day. I don't think earlier versions worked this way, and it seems odd that it asks mne to Read the Online Statement after doing nothing than grabbing the day's latest prices. Is this what everyone is experiencing, and am I the only way to find this odd? Thanks, Paul Pinella Radar Media G...

Q: Referencing named cells in external worksheet ?
Using Excel 2002. I have a workbook with 12 worksheets (one for every month of the year), wherein a lot of the information is looked-up (using VLOOKUP) in simples arrays. I saw no point in implementing the arrays as a 13th worksheet, because I will have a yearly version of my monthly worksheets in one workbook (so one for 2003, 2004, etc). If I change the array(s), I want them to be reflected in all referencing cells. Problem: If my workbook containing my arrays (called "Global") is loaded, I have no problem and the references to it read as: (blabla) 'Global.xls'!Roster ...

how do i change days in a year to 365
How do I chage Excel's standard of 360 days in a year to be 365, for functions such as Days360 and other methods of figuring out how many days occur between 2 dates? The number of days between two dates can be calculated by direct subtraction. For example, if A1 contains 1/1/2004 and A2 contains 1/1/2005 and you enter the formula =A2-A1 in another cell, the result will be 366 (formated as an integer). -- Gary's Student "tripjules" wrote: > How do I chage Excel's standard of 360 days in a year to be 365, for > functions such as Days360 and other methods o...

Visible cell characters
Can I increase the # of characters that are visible in a cell? 67 Excel Help on "limits" or "specifications" reveals that Excel will allow 32,767 characters to be entered in a cell. However, it goes on to state that "only 1024 characters will be visible or can be printed" To work around this limitation, stick a few ALT + ENTERs in at appropriate spots. The ALT + ENTER forces a line-feed and expands the 1024 limit. How far is not really known. Just experiment. .........From Dave Peterson.......... I put this formula in A1: ="xxx"& REPT(REPT(&...

Change background color of query results from access in worksheet
Hi, I have Excel 2007 and selected Date => From Access and then navigated to an Access DB and selected a query. I chose "Table" view. The query results now appear in my worksheet which is great. However, I want to change the background. Right now it alternatives by row between a light blue and a darker blue. I want the results to have a white background with black borders but have been unable to change the format. If I go to Home and select No fill for a color background nothing happens. Is there a way to change the query results? Thanks, Could be the r...

Cell Reference Problem with Network
Let's say I have a spreadsheet with two worksheets = SheetA and SheetB. SheetA might reference a cell in SheetB with a formula like =SheetB!A1 But when I move this to the network the reference changes to include the network drive and file name like: ='Z:\FOLDER\[FILE]SheetB!A1 the file may move from my laptop to the network several times and this becomes completely confusion as the reference looks, not within the same spreadsheet which is what I want it to to, but for another file out on the network. How do I explicitly reference a cell within a difference worksheet but alwa...

How to generate a truly empty cell
"" generates a zero-length string, not a truly empty cell. This is causing problems elsewhere. I'd like to find an output for an IF statement that will give me a truly empty cell. The current formula is: =IF(COUNT(C24:C29)>0,SUM(C24:C29),"") Any ideas? If it involves a macro (as I think it might, having read other posts), please explain how to implement it. Thanks! <This is causing problems elsewhere> It shouldn't. Don't use ISBLANK(A1), use A1="" -- Kind regards, Niek Otten Microsoft MVP - Excel "paulkaye" <paulmjkaye@gm...

Splitting cells
in cell A1 i have the numbers 123456789. i want cell B1 to have numbers 1234567 and cell C1 to have just 89. what is the formula? i have tried text to columns If this is for extracting the first 7 characters use LEFT() =LEFT(A1,7) =RIGHT(A1,2) -- Jacob (MVP - Excel) "fazz" wrote: > in cell A1 i have the numbers 123456789. i want cell B1 to have numbers > 1234567 and cell C1 to have just 89. what is the formula? i have tried text > to columns Hello Jacob - i did not explain this very well.The digits in cell A1 is variable length. In cell C1 i n...

Rounding with no Decimals...
Hello all, I have a cell set up to show a number with no decimals. With decimals the number should be 5555.9854... but when I set it up to show no decimals it returns 5556. is there a way to tell it not to round the whole number at all? Thanks so much, Confused Man "Confused Man" <bfbj@iup.edu> wrote in news:ZdCdnScz65Lm2HTfRVn-tA@comcast.com: > Hello all, > > I have a cell set up to show a number with no decimals. With decimals > the number should be 5555.9854... but when I set it up to show no > decimals it returns 5556. is there a way to tell it no...

Limitations to # of Decimal places for seconds (time)? #2
Bernie Deitrick Wrote: > Xprezons, > > Are you really recording every 1/10 millionth of a second? And, yes > Excel > limits you to three decimal places on the seconds > > Thanks a ton for your help, Bernie. I guess this is what I have to do. And yes, the system does log time to the accuracy of 6 decimal digits. Thanks and Regards, Xprezons -- Xprezon ----------------------------------------------------------------------- Xprezons's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1505 View this thread: http://www.excelforum.com/showthread....

An OL folder has changed from Mail to Calendar
I have a user (using Outlook XP on WinXP, Exchange 2000) who has a folder in their inbox that contains emails. Last week he received an email which went into that folder, and somehow converted it from Post to Appointment format, so he can no longer get to the emails that were previously in there. I've never seen or heard of this before, and I don't seem to be able to manually change it. Has anyone seen this before, and if so, what can I do to get access to the emails it originally contained? Cheers, Gavan Blunt. What happens if you use Advanced Find? --� Milly Staples [MVP - Ou...

FRx Report Date won't change
We have a customer that has a GP Fiscal Year of 9073. Apparently, an AP transaction had accidentally been posted with a document date of 9073. So, in order to void that transaction, they created the fiscal year of 9073. Now, in FRx when they try to change the Report Date to December 2008, FRx changes the report date to December 9072. We've tried deleting the .g32 file and rebuilding the FRx GL Index. We've also tried deleting the SY40100 and SY40101 tables, rebuilding the Fiscal Periods in GP then rebuilding the FRx GL index but we still can use the 12/2008 date in FRx. Any...

sum and times within text boxes
I want to multiply text boxs ie 4(1box)x $1.00(2box)=Total $4.00 (3box). I also want sum total (5 text boxes) for grand total. Is this feasible on form?? Thanks On Mon, 14 Jan 2008 18:58:01 -0800, He cries for help <Hecriesforhelp@discussions.microsoft.com> wrote: Have you tried an expression like: =[1box] * [2box] (assuming your control names are 1box and 2box. This expression would go in 3box' Control Source property. -Tom. >I want to multiply text boxs ie 4(1box)x $1.00(2box)=Total $4.00 (3box). >I also want sum total (5 text boxes) for grand total. > >Is this ...

Validation rule to prevent overlapping time periods
I have a table: InactID-AutoNumber PreceptorID-Number InactStart-Date/Time InactEnd-Date/Time I don't want records that have overlapping time periods for a PreceptorId. I tried: [InactStart] < (DLookUp("[InactEnd]","tblInactive","[PreceptorID]=" & [PreceptorID] & " and [InactID]=" & [InactID]-1)) but found that the DLOOKUP function was not permitted in a table validation rule. Any suggestions? You won't be able to use a table validation rule here. Instead, use the BeforeUpdate event procedure of the *form* where the data...

why does wrord change the origianl date when I open a saved doc
I do not want word to change the date of a saved doc when i open it I assume that the reason is that you have inserted a date field which shows the system date. What you need to do is change those date fields for createdate fields - ALT+F9 change { DATE } or { TIME \@ "d MMM yyyy" } to { CREATEDATE \@ "d MMM yyyy" } then F9 and ALT+F9 - and change the date in your letterhead template so that future letters based on it show the correct dates. The switch \@ "d MMM yyyy" may be different at your location. See http://www.gmayor.com/stop_automat...

making a sum outcome negative based on adjacent cell value
I have 3 columns of single figures. At present i'm using the sumproduc fuction to multiply and total the figures in column A that fall betwee 4 and 9 with the adjacent figure in column B... =SUMPRODUCT(--($A1:$A600>=4),--($A1:$A600<=9),($B1:$B600),($A1:$A600) I'd like to add column C to the formula, so that if it contained value of -1, 1 or 2, the sum of the adjacent figures in columns A and appears as a negative number. For example A3= 7, B3= 2, C3= 1 Outcome= -14 A4= 9, B4= 1, C4= 5 Outcome= 9 A5= 3, B5= 2, C5= 2 No sum because figure in column A ...

Setting multiple meeting times for different attendees on the same day
I am a recruiter and use Outlook to schedule interviews with out of town candidates. It's critical that all my interviewers be available on the same date but at different times during the day. I would like to be able to pull up all of my interviewers' schedules at the same time and schedule them in different time slots but only having to send one meeting request. Example: I have 5 managers that will be interviewing the same person on the same day at different times. I have to list them all as "required attendees", check their schedules, then back out and send o...

SPPS
Hi We have SharePoint Portal Server 2007 english version. Duo to our localizatino, we need to change it to native Slovenian language. Is this possible and what are the steps to do this? Regards, Miha ------=_NextPart_0001_F428B286 Content-Type: text/plain Content-Transfer-Encoding: 7bit Once a site is created you cannot change its language. You would need to create a second site in the alternate language. Steps to do this are located here: http://technet.microsoft.com/en-us/library/cc287692.aspx Note: There are a few blog entries and newsgroup posts that talk ab...

Merged Cells
I have imported data into Excel. The left-hand column has merged cells containing a reference number. The remaining columns contain varying records associated with the reference number, a one to many ratio. I need to display the worksheet so that the appropriate reference number is displayed in the left-hand column for each of the records in the worksheet. There are hundreds of reference numbers. Is there an automated way to do this besides unmerging each section and copying the reference number into the now unmerged cells? Thank you. ...

HOW to COUNT THE FREQUENCY of specific CHARACTER WITHIN a CELL?
How can a frequency of a specific character be counted with in a cell. Ex -" #4 Bluebirds, #6 Aquatic" is in cell B2 - how do I count the number of "#" that appear in cell B2? =LEN(B2)-LEN(SUBSTITUTE(B2,"#","")) HTH Jason Atlanta, GA >-----Original Message----- >How can a frequency of a specific character be counted with in a cell. Ex -" >#4 Bluebirds, #6 Aquatic" is in cell B2 - how do I count the number of "#" >that appear in cell B2? >. > ...