Formatting decimal numbers as time - no conversion to time

I have a timesheet that's used for payroll purposes.  The sheet is about as 
simple as it can get.

I would like to be able to enter the time worked for XXXXXX as a decimal 
number and have it display as hours and minutes.  For example:

4.5 hours would display as 4:30
2.1 hours would display as 2:06

For any computation of the time, such as adding the two time examples above, 
the calculations would work with the original decimal numbers, and the total 
would yield 6.6 hours of time.  Yet the display in the cell would be 6:36

You would think this would be a relatively easy thing to do, possibly with 
the custom number format.  But I've not been able to find any explanation of 
the custom number formatting process that I understand, nor have I been able 
to locate an example.

Is it easy to do?  How do you do it?  I would prefer NOT to have to have 
hidden rows and columns, or any spiffy basic programming involved, as I would 
like to pass on the finished product to others who know even less about Excel 
than I do.   :-)


Thanks.

Ken
0
9/13/2006 3:27:01 AM
excel 39879 articles. 2 followers. Follow

3 Replies
343 Views

Similar Articles

[PageSpeed] 6

Well, to do what you want you will need either/or:

> hidden rows and columns, or any spiffy basic programming involved

Biff

"Ken Springer" <wordworks.nospam@greeleynet.com> wrote in message 
news:94099E58-18C5-4240-B8C4-680F72E1876C@microsoft.com...
>I have a timesheet that's used for payroll purposes.  The sheet is about as
> simple as it can get.
>
> I would like to be able to enter the time worked for XXXXXX as a decimal
> number and have it display as hours and minutes.  For example:
>
> 4.5 hours would display as 4:30
> 2.1 hours would display as 2:06
>
> For any computation of the time, such as adding the two time examples 
> above,
> the calculations would work with the original decimal numbers, and the 
> total
> would yield 6.6 hours of time.  Yet the display in the cell would be 6:36
>
> You would think this would be a relatively easy thing to do, possibly with
> the custom number format.  But I've not been able to find any explanation 
> of
> the custom number formatting process that I understand, nor have I been 
> able
> to locate an example.
>
> Is it easy to do?  How do you do it?  I would prefer NOT to have to have
> hidden rows and columns, or any spiffy basic programming involved, as I 
> would
> like to pass on the finished product to others who know even less about 
> Excel
> than I do.   :-)
>
>
> Thanks.
>
> Ken 


0
biffinpitt (3172)
9/13/2006 5:54:53 AM
Ken,

You can't enter hours as a decimal number into a cell and have it display as 
hh:mm in the same cell.  Excel's date-time formatting is based on units of 
days.  The date-time formatting depends on it.  4.5 would always be 4 1/2 
days.

Another cell can display it as hh:mm with:
=A2/24   where A2 contained the hours as decimal numbers.  You'd format the 
cell with Format - Cells - Number tab - Custom, and type in a format code 
like: [hh]:mm

The total could be:
=SUM(A2:Awhatever)/24   formatted similarly as above.  So if you're willing 
to put your decimal numbers in separate cells, such as in a separate column, 
you can do it.  Consider that solution

For a good treatise on Excel's date-time formatting, see 
http://www.cpearson.com/excel/datetime.htm#SerialDates
-- 
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Ken Springer" <wordworks.nospam@greeleynet.com> wrote in message 
news:94099E58-18C5-4240-B8C4-680F72E1876C@microsoft.com...
>I have a timesheet that's used for payroll purposes.  The sheet is about as
> simple as it can get.
>
> I would like to be able to enter the time worked for XXXXXX as a decimal
> number and have it display as hours and minutes.  For example:
>
> 4.5 hours would display as 4:30
> 2.1 hours would display as 2:06
>
> For any computation of the time, such as adding the two time examples 
> above,
> the calculations would work with the original decimal numbers, and the 
> total
> would yield 6.6 hours of time.  Yet the display in the cell would be 6:36
>
> You would think this would be a relatively easy thing to do, possibly with
> the custom number format.  But I've not been able to find any explanation 
> of
> the custom number formatting process that I understand, nor have I been 
> able
> to locate an example.
>
> Is it easy to do?  How do you do it?  I would prefer NOT to have to have
> hidden rows and columns, or any spiffy basic programming involved, as I 
> would
> like to pass on the finished product to others who know even less about 
> Excel
> than I do.   :-)
>
>
> Thanks.
>
> Ken 


0
someone798 (944)
9/13/2006 6:07:29 AM
Biff and Earl,

Thanks for the replies, but heading down that road is not a good thing for 
my situation.  If I was going to be the only one using the timesheet, I'd do 
it that way.  But creating a sheet with those kinds of things would confuse a 
lot of people that will ask me for a copy.

I just want it to look like time, not be treated like time.  :-(

You would think that enough people here would have asked over time (oh, what 
a bad pun!) for this ability that Microsoft would have built this in.  It's 
really a simple concept when you think about it.

Thanks again for the replies.



Ken

0
9/14/2006 4:08:02 AM
Reply:

Similar Artilces:

Time scale axis should be available in PivotChart Report.
We should be able to time scale the X axis in PivotChart Reports. Pivot Charts are very usefull. I don't see any good reason to lose the time scale propety while doing a PivotChart Report. > Pivot Charts are very useful. I guess I don't agree. It could have beenn true, except for the severe limitations in Pivot Charts, one of which is the topic of your post, which render them nearly useless in most nontrivial applications. The only time I make pivot charts, in fact, is to answer posts in this group. I make regular charts from pivot table data. This is not too hard, as long...

I can't format 01/19/2004 to read January 19, 2004, please help!!
Dragged and dropped a series of dates and now I can't convert them to sort them properly. What to do?? Hi Paul, "paulonline66" <paulonline66@discussions.microsoft.com> wrote in message news:3BB832DA-34AE-44B4-9D73-6F178E9AEBF0@microsoft.com... > I can't format 01/19/2004 to read January 19, 2004, please help!! > Dragged and dropped a series of dates and now I can't convert them to sort > them properly. What to do?? With the date cells selected: Data | Text to Columns | Next | Next | Select the Date option and Select the DMY option in the dropdown...

Summing Short Time data type
i've imported a column of short date times like below and trying to add them up. Is there a certain format that can be used to add them? I just get 00:00 as the total. 00:10 '10 minutes 00:07 ' 7 minutes Maybe they have imported as text? That would explain the 00:00 If I put 00:10 in A1 and 00:07 in A2 and do =SUM(A1:A2) I get 00:17 press F5, click special, select constants and uncheck everything but text, if they get selected they are text -- Regards, Peo Sjoblom "Scott" <sbailey@mileslumber.com> wrote in message news:OCV3rP$pDHA.1444@tk2msftngp...

Sending HTML format report is not working #2
I have been sending RTF report from Ms Access on the body of the email. I need to send it in HTML format instead, but it is not working. The body of the email displays the whole source instead of the HTML formatted body. I have realized that the email format shows "Plain Text" and "Rich Text" format only. If you have further questions, please do not hesitate to contact me. Below there is an example of what I am getting, please check: <HTML><HEAD><META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-1252"><TITLE>Renta...

Custom number format
Hello. At work we have an application that creates .csv files that we open with Excel. This list icludes product nubers that are ten digits long, many of which start with one or more zeros. For legibility I would like all of the numbers to show leading zeros if they have them. So I create a custom format of ten zeros (i.e. 0000000000). This works great, but the format is only in that workbook. Is there a way to create this custom format and have it always be available even when a new notebook is opened insted of having to re-create it every time? If so, can it be assigned to a new toolbutton...

Automate Numbers
I am looking for advice for the best way to solve a problem. I am developing a system of telephony extensions etc at a workplace, some sites may have 200+. I am trying to automate entering all the extension numbers, rather than manually inputting them. I also have a format for the range of extensions, but don't know if Access will allow me to give an example and then create the extension for me automatically in sequence. Thanks, Lisa W. Sounds simple enough.. what is your format? "Lisa W." wrote: > I am looking for advice for the best way to solve a problem. > ...

Image and File Conversion Problem
When I convert my poster file to a PDF for pre-press the file created does not include the hi-res photo image I included in the design. It includes all other graphic elements, text boxes, etc. Not sure what to do. Anybody who has had this issue or has a reccomendation would be greatly appreciated. ...

Need grayscale to monchrome conversion
Sorry for the non-mfc specific question but could someone point me to an article or group that might have info on converting a grayscale bitmap to monochrome? I've imported some code from IJG that converts jpg to grayscale but I need monochrome on a non-windows system. Thanks and again sorry for the inconvenience. isn't a grayscale bitmap also monocrhome by default?? "MAO" <nospamplease.com> wrote in message news:eC$b85l%23EHA.2568@TK2MSFTNGP10.phx.gbl... > Sorry for the non-mfc specific question but could someone point me to an > article or group that mig...

Footer date format #3
How do I change the date format for my excel worksheet footers? Hi "imran", Change your Regional Date format in your system Control Settings. Or use a macro http://www.mvps.org/dmcritchie/excel/pathname.htm#modify --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Imran" <Imran@discussions.microsoft.com> wrote in message news:A1187E71-49C7-4DA1-9456-912FFEB92B2C@microsoft.com... > How do I change the d...

Reg: Method execution time
Hi All, I wanted to calculate the time take for execution of method based on CPU Instruction execution Speed. Is it possible to estimate total time to be taken for the following code, before executing the code? are there are APIs provided for this purpose? void Run() { int i = 100; while(i) { printf(" cnt %d\n",i); i--; } } thanks in advance, Chandrasekhar <cs1975@gmail.com> wrote in message news:40f825c8-7b14-42df-aa45-6744ff8dacae@b2g2000hsg.googlegroups.com... > Hi All, > > I wanted to calculate the time take for execution of method based on > CPU ...

Text to Date Conversion
This is probably an age old question, so apologies in advance... I'm trying to import data into Excel from a csv file that contains a date field of the format "Mon 01 Jun". There seems to be no easy way to get Excel to recognise this as a date, unless anyone can tell me differently. Assuming there isn't a quick import / text-to-columns conversion, what is the easiest way with a function to create a new cell that contains a date from this text? Thanks, Richard. With the text in A1; assuming all months are 3 characters =DATE(2009,MATCH(RIGHT(A1,3),{"Jan",&q...

conditional format formula? help please
Folks, I would like to format an entire row in my sheet based on the text in one cell of each row. For example, when cell S33 equals "Complete", I'd like row 33 (or even just cells S1 to Snn) to assume a gray background. Now I'm no stranger to Excel and routinely build mildly complex formulas and arrays, but I can't seem to make this work in the conditional format dialog box using the "Formula is" choice. What am I missing? Anyone have a quick formula I can put in the dialog? thanks, -gb Geoff, Select Row 33 Format -> Conditional Formatting... Formu...

A little bug in my macro about time (excel 2007)
Hello, I'm a senior computeruser from Belgium and I've made a macro where you can only see the time but there is a little bug in it. That bug shows up when I use my macro for the second time in a other cell. When I used it for the second time, the cell where I used my macro for the first time changes into the same time of the cell where I used my macro for the second time. This is how I made my macro: - I wrote in my formulebox --> =Nu()-Vandaag() [translation --> =Now()...

time between asking for mails about pop3
Hi, i search a possibility to decrease the time between two queries from our Exchange Server to the pop3-Server under 15min. This lower border seems to be hard coded in the exchange server, or is there somewhere in the reg a possibility to lower this timeinterval? Greetings, Andreas Grund "Andreas Grund" <anonymous@discussions.microsoft.com> wrote: >i search a possibility to decrease the time between two >queries from our Exchange Server to the pop3-Server under >15min. This lower border seems to be hard coded in the >exchange server, or is there somewher...

universal formatting of all cells in all worksheets
I have 140 sheets in an excel document. In column AF I want the width to be set to 15.86. Instead of going to every sheet and making the change, is there a way that I can set the width for that column in all of the sheets right click on a sheet tab>select all sheets>set whatever on the sheet you are on>select any One sheet. -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "jeremyrod" <jeremyrodriguez@cmsmechanical.com> wrote in message news:0b4301c351e9$39fe41e0$a501280a@phx.gbl... > I have 140 sheets in an excel document. In column AF I >...

Will excel 2003 format a cell to the year?
It seems like it should be obvious, but I am finding it impossible to get excel to do a date format that recognizes a year. Is there some way? I typed a date in A1 (09/28/2006) and used format|Cells|number tab|custom category|yyyy with my USA settings. kateofmd@msn.com wrote: > > It seems like it should be obvious, but I am finding it impossible to > get excel to do a date format that recognizes a year. Is there some > way? -- Dave Peterson ...

Time Sheets #2
I have looked at various Time calculation examples but have not seen this really addressed Is there a way to have a time sheet where people do not need to enter time using military time or using AM or PM in the actual entry. We have a business that is a normal 9 - 5 business so there is no overnight or really late night work hours. If start time is in C2 , lunch out in D2, lunch in in E2, time out in F2. Normal time calculation is (F2-E2+D2-C2)*24 Am trying to use (IF(F2>E2,F2-E2,(E2+12)-E2)+If(D2>C2,D2-C2,(D2+12)-C2))*24 but am not getting correct answer (Trying to add 12 hours ...

Text or Number???
Excel (2003) is converting data I enter to a different format. I have a unique identifier that I use in a VLookup, 12 digit text. This is a series of digits 12345678.. or 01234556... 1) If I enter this as text, Excel (2003) converts it to scientif format. So I am forced to format it as a number. 2) I have to enter return after the ID in each row or the VLookup returns #N/A. This is impracticle for thousands of records. I should have not have to do it even for a few records??? Any suggestions?? If you want text you need to performat the cells as text first, or preface the digits with ...

I just want to find the number or way to pay for MS support on
I just want to find the number or way to pay for MS support on Outlook 2007. I can not find a link or number. Help! martinbrossman wrote: > I just want to find the number or way to pay for MS support on Outlook 2007. > I can not find a link or number. Help! So let's see what happens when I follow the obvious links ... Went to http://www.microsoft.com/. Clicked the "Contact Us" link at the bottom. Clicked on the "View Customer Service Solution Centers" link. Technical Support is not explicitly identified on this page. Clicked on the Contacts lin...

Detecting section numbers
I have a Word 2007 document and want to use a macro (VBA) to detect which section the cursor is in. Does anyone know how to do this? Most operations involving Sections seem to require the section number as an argument, but I just want to determine which section the cursor is currently located in. Many thanks, Geoff MsgBox ActiveDocument.Range(0, Selection.Sections(1).Range.End).Sections.Count Geoff Budd wrote: > I have a Word 2007 document and want to use a macro (VBA) to detect > which section the cursor is in. Does anyone know how to do this? > Most operat...

CRM 4, Rollup 5
We are trying to install rollup 5 on some of our PCs (the server upgrade was successful) and on most of them the installation seems to have been successful (the success screen is displayed) but the version number displayed in Outlook 2003 when the CRM button is clicked shows 4.0.7333.3 rather than the expected 4.0.7333.1644. Is there an issue with installing rollup 5 that we are unaware of? We have not installed any previous rollups but the KB article says 5 is incremental and the earlier rollups are not required. OS: XP Pro SP2 Outlook: 2003 SP3 Thanks, Tony. ...

Conditional Formatting
In general I was wondering if conditional formatting has any detrimental impact on database performance. Is this something I should use to highlight particular exceptions or data that falls outside the bounds of a certain range or is there something else that will work better? Terrence, Use Conditional Formatting quite frequently and have never noticed any slow down. The only issue has ever been been if you want more than three conditions then you'll have to use VBA to *simulate* the built in functionality. -- Gina Whipp 2010 Microsoft MVP (Access) "I fee...

Format Label as "Standard" Number
I'm using Queries to populate a Report w/ numbers using the label's caption. The financial Report uses various Queries. If a label.caption populates as 1234.56, how can I get it to appear as 1,234.56?? And, if needed later, as $1,234.56. I've tried various CCur, and formatting as Standard, in code. TIA - Bob On Thu, 7 Feb 2008 14:49:01 -0800, Bob Barnes <BobBarnes@discussions.microsoft.com> wrote: >I'm using Queries to populate a Report w/ numbers using the label's caption. >The financial Report uses various Queries. Why label captions, rather than the mu...

Last non-zero value in a vertical list of numbers
I want a formula that will identify the row number of the last non-zero value in a vertical list of numbers [non-sequential, cannot sort list and many zeros within list] For example Row Value 1 789 2 0 3 0 4 456 5 1234 6 23 7 345 8 0 In this case the answer is row 7 Please note I don't want to add extra columns or use macros Thanks in advance! Neil Hi try the following formula: =LOOKUP(2,1/(A1:A1000<>0),ROW(A1:A100)) >-----Original Message----- >I want a formula that will identify the row number of the last >non-zer...

comparitive time period reporting
I am interested in the best value way to get comparative reporting which for example would compare April 2006 with April 2007 sales for all my stores in an easy to read way, perhaps by department/catagory/suuppier. I've asked my reseller about this, but they have quoted me �4000+ to develop this sort of reporting pack. Any advise? -- Philip Gass Donaghadee Garden Centre Philip Gass - Creative Gardens - wrote: > I am interested in the best value way to get comparative reporting which for > example would compare April 2006 with April 2007 sales for all my stores in > an ...