Transfer time hh:mm to decimal and round

Anyone know how to transfer a time (hh:mm) into a rounded decimal? 
example:  3:08 to be 3.25??

--
Message posted from http://www.ExcelForum.com

0
6/18/2004 6:28:27 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
1007 Views

Similar Articles

[PageSpeed] 26

I'm not sure if this is what you're asking, but if I have my time i
cell A1, my formula might look like this...

=(HOUR(A1)*60+MINUTE(A1))/60

Format the cell as "number" showing as many decimals as you need. Th
time conversions would look like this:

3:15 would yield 3.25

4:30 would yield 4.50 (with decimals set at 2)

13:12 would yield 13.20...etc.

I'm assuming you want the decimals to show the minutes as fractions o
an hour...if not, this isn't what you want

--
Message posted from http://www.ExcelForum.com

0
6/18/2004 7:24:35 PM
This does work to get it to a decimal, but I need it also to round t
the nearest quarter hour.
3.0
3.25
3.5
3.75
Any other ideas

--
Message posted from http://www.ExcelForum.com

0
6/18/2004 8:04:30 PM
I'm assuming that you want to do something like take a time card an
convert the hours worked to the nearest quarter hour. If I'm correct
this ridiculously long formula seems to work OK. If your data (times
are in Column A, your formula would look like this:

=IF(MINUTE(A1)>=53,HOUR(A1)+1&"."&"00",HOUR(A1)&"."&IF(MINUTE(A1)<=7,0,IF(MINUTE(A1)<=22,25,IF(MINUTE(A1)<=37,50,IF(MINUTE(A1)<=52,75,0)))))

This gets the following results on my spreadsheet (formatting the cell
as "number" and showing 2 decimals:

3:30 yields 3.50

15:08 yields 15.25

7:53 yields 8.00

4:21 yields 4.25

18:39 yields 18.75

You may have to adjust the values to round the way you want, but 
think it should work

--
Message posted from http://www.ExcelForum.com

0
6/18/2004 10:10:43 PM
Just following your lead of converting to decimals, this should round to
what's being asked for:

=ROUND(((HOUR(A1)*60+MINUTE(A1))/60)*4,0)/4
-- 

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"Llobid >" <<Llobid.182hbt@excelforum-nospam.com> wrote in message
news:Llobid.182hbt@excelforum-nospam.com...
I'm assuming that you want to do something like take a time card and
convert the hours worked to the nearest quarter hour. If I'm correct,
this ridiculously long formula seems to work OK. If your data (times)
are in Column A, your formula would look like this:

=IF(MINUTE(A1)>=53,HOUR(A1)+1&"."&"00",HOUR(A1)&"."&IF(MINUTE(A1)<=7,0,IF(MI
NUTE(A1)<=22,25,IF(MINUTE(A1)<=37,50,IF(MINUTE(A1)<=52,75,0)))))

This gets the following results on my spreadsheet (formatting the cells
as "number" and showing 2 decimals:

3:30 yields 3.50

15:08 yields 15.25

7:53 yields 8.00

4:21 yields 4.25

18:39 yields 18.75

You may have to adjust the values to round the way you want, but I
think it should work.


---
Message posted from http://www.ExcelForum.com/

0
ragdyer1 (4060)
6/19/2004 12:29:45 AM
Reply:

Similar Artilces:

Decimal place in Physical% Complete
Is there any way to add decimals to the Phy% complete column? This has a significant impact on high dollar tasks when calculating BCWP. It's either round up to next or round down and that can be significant. Thanks, AnthemAz AnthemAZ -- Sorry to disappoint you, but Microsoft Project always rounds off the Physical % Complete value to the nearest integer. Hope this helps. -- Dale A. Howard [MVP] VP of Educational Services msProjectExperts http://www.msprojectexperts.com http://www.projectserverexperts.com "We write the books on Project Server" &qu...

resource time
Hi, Is there documented information (MS or other) for how much resource time is required to support common exchange server and client tasks? Basically the day to day tasks. The only documented information i have come across is that it takes about .25 Administrators for every 100 users. thanks. wow, where did you find that statistic? I'd like to show it to my boss...I single-handedly support over 8,000 users here, and also assist with support for two remote sites...that being said, Exchange support (excluding support for Outlook issues, which is a lot of what I have to do) boils down...

saving the same file in different places at a time
hi, i have an excel file in one system when i update this file, is it possible some range of cells in this file to be saved in the different system also how do i give a link to the other file? appreciate, if anyone could help Sounds like you want to paste a link so that the cell will update every time the other cell gets new info... To do this, say you want to link cell A1 to cell A7...click on cell A7 and click copy...Go to cell A1 and click paste...Then (if you have Excel 2003), click on Edit/Paste Special/Paste Link and you now have youe link...The "Paste Link" Button ...

round buttons in Excel?
Hey all -- I am setting up a complicated Excel workbook with an opening menu page featuring buttons leading to specific areas of interest. That part I have down cold -- my question is, do buttons have to be rectangular? To fit with departmental preferences I'd like to create a series of oval buttons. Any suggestions? > That part I have down cold Great -- in that case, just use stuff from the Drawing toolbar instead of a button (eg: an oval and a text box grouped). HTH, Andy ...

Time chart
I need a time chart as in example A1= Henry B1=15 C1=21 A2= Sally B2=12 C2-=18 A3= George B3=11 C3=16 A4= Alex B4=10 C4=14 Henry ------------------------ Sally ----------------------- George -------------------- Alex ----------------- 10 11 12 13 14 15 16 17 18 19 20 21 You could simulate the chart with an XY Scatter chart of the data 15 4 21 4 12 3 18 3 11 2 16 2 10 1 14 1 Format the y-axes to show no labe...

Time input mask format
If I input - "340 a", I want it to show "3:40 AM" or if I input 1230 p, I want it formated as 12:30 PM can this be done? enter your times as 3:40 or 12:30 and set the format of the cell to show it as am or pm format ,,,, cells,,,,custom,,, h:mm AM/PM "WNB-96740" wrote: > If I input - "340 a", I want it to show "3:40 AM" > > or if I input 1230 p, I want it formated as 12:30 PM > > can this be done? There is no way to set up an input mask as you describe without using VBA. See Chip Pearson's site for quicken...

Multiple time ranges
I am trying to create a formula that will look up the time values and return a letter. Times are in column F. Must include seconds (as values does include seconds). 07:00:00 - 17:30:00 = "A" 17:31:00 - 00:30:00 = "B" 00:31:00 - 06:59:00 = "C" <as values does include seconds).> But you miss out whole minutes in your specications. Anyway, this should work, but do check your threshold values. Maybe you need to add a second or less than a second. Set up a table like this (in this example: A1:B4) 0:00:00 B 0:30:00 C 7:00:00 A 17:30:00 B With your time to l...

How do I set up a template to save time typing names
I have to send a lot of e-mails to the same people, and always more than one person. How do I set up a template so that I don't have to keep typing who it is to and who I am 'Cc' to? traceyella <traceyella@discussions.microsoft.com> wrote: > I have to send a lot of e-mails to the same people, and always more > than one person. How do I set up a template so that I don't have to > keep typing who it is to and who I am 'Cc' to? Create your message, but instead of sending it, save it as an OFT. WHen you want to send it, forward it from your Drafts fold...

How do I transfer OLEXP folders?
I have to computers, one which I just had to format because of endless problems. Now I want to get my Outlook Express set back up, is there any way I can transfer folders and messages from one Outlook Express 6 to another Outlook Express 6 on a network? I tried the Export option and got this messages "An error occurred while initalizing MAPI. Any help??? "Kevin" <carrottop_15@hotmail.com> wrote in message news:ac7b01c43693$89a28b40$a101280a@phx.gbl... > I have to computers, one which I just had to format > because of endless problems. Now I want to get my Ou...

Ref Transfering Info from a table
I don't know if I'm asking the impossible here but I'll give it a try. I have a excel program which I am trying to set up a function whereby it will create a work rota based on a shift pattern. It currently will create a table with staff names on it and there shift pattern for a given week. What I am trying to do is let the user select shift pattern 1 for example and have excel transfer this onto the rota but place the name instead of shift in the appropriate shift. ie If I select shift 1 it will place name A onto the early shift and place A on the Day off line whi...

Length of time to publish
Does anyone know why it takes a very long time to publish my websites ? Is it something I'm doing wrong or is my computer slow ? Many variables. The speed of your internet connection. Traffic on the internet. And, the host may have it throttled to a certain amount. -- Tom [Pepper] Willett Microsoft MVP - FrontPage Since 1997 --------------------------- "mousey" <mousey@discussions.microsoft.com> wrote in message news:90A5062A-42EB-4039-8E24-20D26F260CB7@microsoft.com... : Does anyone know why it takes a very long time to publish my websites ? : Is it someth...

Strange. Deleted Journal and Time Clock
I just tried to do payroll and all of my payroll entries before 7/1/07 are gone. Also the journals before that date are also missing. All of my items are still in my register list. What gives? ...

transfer shares from one a/c to another, lose history
Hi, To transfer shares from one investment account to another, which is the best option to use? I have been using the transfer out option, but by doing that, all the transaction history is lost. Is there a way to preserve it? Thanks. It's still preserved in the original account. "ab" <nospam@nospam.hotmail.com> wrote in message news:26qqf.4339$Ou3.1708@dukeread09... > To transfer shares from one investment account to another, which is the > best option to use? I have been using the transfer out option, but by > doing that, all the transaction history is l...

Mass change of transaction types to Transfer trouble. Suggestions
background: I am currently using Money 05 and have transfers forms on. In 2002 I had money all up to date in my banking life and stopped using it because of one problem or another. I just logged onto each of my banks and downloded all the missing information to date. In one bank I have a checking and savings account in. The file I downloaded from the bank has transfers incorrectly marked as deposits and withdrawls and nothing linked together. I also have money 07 trial installed on a different pc running a copy of the above database (for testing.) What I want to do is select all ...

Re-format date (mm/dd/yyyy into dd/mm/yyyy)
Hi All, Could someone help me with the following: I have a range of dates which I want to convert from mm/dd/yyyy into dd/mm/yyyy, my formula (=MID(T1474,LEN(T1474)-6,2)&"/"&LEFT(T1474,2)&"/"&RIGHT(T1474,4)) works for most of the dates though in case of 1/12/2007 where the month only has one position (instead of 01) my formula takes "1/" for mm (instead of "01") Can one of you advice how to capture this issue in my (or a different) formula? Many thanks!!!! Rgds, Robert You could try: Data > Text to Columns > Next > Next >...

Sort by state AND time zone?!
Calling all creative Excel users! I have client phone numbers in an Excel report, and want to somehow link the phone number to its time zone--and then sort the report by time zones! The purpose is to be able to assign outbound calls to staff at different times of the day, depending on time zone we are reaching I'm possibly an advanced beginner in the user of Excel, so don't be afraid to be VERY detailed in your suggestion for a formula! I don't know how to use pivot tables, by the way.. Many thanks Kay Provided you can find a list of area codes/time zones, you could use ano...

Rounding for order size
I'm doing a forecast using some formulas. I want the result to take in consideration the minimun order size. For example if my forecast is 38 I want the result to be expressed as 48 which is the minimun package size. The same for example if forecast is 500 I want it to show 480 or 528 which are the actual amounts that I can request. How can I do this? FA Hi! So, what you want to do is round to the nearest multiple of 48? =ROUND(A1/48,0)*48 Biff "FA" <FA@discussions.microsoft.com> wrote in message news:F6E4BD59-5B85-487E-98B7-96E568FB1E44@microsoft.com......

Accrue vacation & sick time
I am getting an error message when I try to accrue vacation and sick time in H/R accrue. The message reads - Cannot accrue attendance for a date that isn't within and accrual period. This will be the second payroll for the new year and I did not have any problems for the first payroll for 2010 ...

Count records b/w time range
I have a sheet which has data with date & time fields like this 19/10/2005 10:10 19/10/2005 11:30 19/10/2005 12:12 19/10/2005 12:15 19/10/2005 10:12 How do I make a formula to pull out count between each half hour slot? like: 10:00 - 10:30 =2 10:30 - 11:00 =0 11:00 - 11:30 =1 11:30 - 12:00 =0 12:00 - 12:30 =2 Please help. Cheers! Sunny =sumproduct(--(B1:B100>--"10:00:00"),--(B1:B100<=--"10:30:00")) -- HTH RP (remove nothere from the email address if mailing direct) <sunilkeswani@gmail.com> wrote in message news:1131569006.191782.216510@g44g200...

transfer publisher template to word
i have a made a greeting card from a template a friend has offered to print them out but does not have publisher on his computer but has word on his computer is there any way to change the format so he can open itv in word?? paul 1 wrote: > i have a made a greeting card from a template a friend has offered to print > them out but does not have publisher on his computer but has word on his > computer is there any way to change the format so he can open itv in word?? You would have to copy/paste the template on your computer, by opening the Publisher template, then opening Wo...

URGENT: Run Time Error '429' in Store Operations Administrator
I'm constantly getting a run-time error 429 - ActiveX can't create object - when trying to run Store Operations Administrator on my back office PC. The Store Operations Manager also fails on the back office PC - can't connect to database -. The register (POS) is still functioning as is the server where the RMS database resides. How do I fix this? I've never seen Active-X errors from Administrator. I'd try uninstalling RMS on that system then reinstalling it. Also make sure you apply whatever service pack you're running on your other systems. Sometimes POS ...

Transfering Messages from Windows Live Mail to Outlook
I have a customer who is using Windows Live Mail (WLM). He has 142 441 files that take 13.3Gb. He keeps on loosing contacts everytime he quits WLM. In addition, He uses the signature feature to preprogram replies to his emails. With WLM when you reply, they all disappear and only the default signature remains. Finally, everytime he starts WLM, it is very slow because it scans his 142 441 files all the time. So I recommended to use Outlook. However, it is limited to 2Gb when we try to import data from WLM. Can you recommend a solution? -- Christopher Kozely EURL US Info In W...

Formatting 'Hours' in work field with out decimals
I need help in formating the work hours showing rounded off to next hr, rather than showing with one decimal. Appreciate help What about using the Round function? Round([Field],0) - or something like that syntax. - Andrew Lavinsky Blog: http://blogs.catapultsystems.com/epm > I need help in formating the work hours showing rounded off to next > hr, rather than showing with one decimal. Appreciate help > On Jan 26, 2:05=A0pm, drjayr2002 <drjayr2...@discussions.microsoft.com> wrote: > I need help in formating the work hours showing rounded off to next ...

Formula to say Yes or No based on variance in time cells
Hi all, I have 2 time cells, F5 and I5, I want a forumla that will return the result Yes or No if there is a specified difference, 30mins for example. We have a spreadsheet with the same information coming from 2 sources and need to compare the information to say wether it is similar or not. Thanks in advance for any help Absolute difference? =IF(ABS(F5-I5)>=TIME(0,30,0),"Yes","No") -- HTH Bob Phillips "KingCreole" <shamble@gmail.com> wrote in message news:1117014247.379550.142950@z14g2000cwz.googlegroups.com... > Hi all, > > I have 2 ti...

Formular for Times
Hi Im looking for away to add working hour, that go into the nex day, eg. 22:30 till 07:15. =sum(B1-A1) only seems to work when hours are all in 1 day. Im using excel 2002 Any suggestions would be much appreciated ! Kenn -- Message posted from http://www.ExcelForum.com On Mon, 26 Jul 2004 05:40:00 -0500, Kenno <<Kenno.19zyom@excelforum-nospam.com>> wrote: >Hi >Im looking for away to add working hour, that go into the next >day, eg. 22:30 till 07:15. >=sum(B1-A1) only seems to work when hours are all in 1 day. >Im using excel 2002 > >Any sugges...