#1
|
|||
|
|||
Cell formatting for time - Excel 2010
Hi All,
I cannot seem to get the formatting of time correct in my spreadsheet. I try to set the cell format to 'mm' for minutes only but everytime I place a figure like 20, into the cell I get '01' showing in the cell and 20/01/1900 showing in the formula bar. No matter how I try to put a figure into the cell it just will not show up correctly, subsequently, I cannot get the right answers when adding minute together. Can Excel perform this type of operation or is it just for show. Best Rgds Dave R |
#2
|
|||
|
|||
you can find details here...
http://support.microsoft.com/kb/266691 |
#3
|
|||
|
|||
Hi
You need, first of all, to understand how Excel stores time/date entries. Excel stores times and dates as an integer from 1 January 1900. So if you formatted a cell for example as days ("dd") and typed the figure 10 in that cell you would have 10/01/1900 in the formula bar. Why do you want to format a cell with a format of minutes? If you explain why you need this we may be able to help you further. Tony(OTPM) |
#4
|
|||
|
|||
Hi Catalin.B & OTPM,
I'll check out the link but in the mean time I have a time sheet to prepare for staff and pay office and I wanted to put in/out times on a spreadsheet, calculate the hours worked, time off for breaks etc. Best Rgds, Dave R |
#5
|
|||
|
|||
Hi
In that case you need to format your start and finish collumns as follows: dd/mm/yyyy hh:mm The enter your start and end times in that format. Then in your result column format your cells as: hh:mm You can then subtract the Start column from the Finish column in your results column and you will get the number of hours and minutes your staff worked. Hope this helps. Good luck. Tony(OTPM) |
#6
|
|||
|
|||
Hi Tony,
Thanks for that I'll give it a whirl and see how I get on. It's been a long while and several versions ago that I got into cell formats and for the life of me I just couldn't see how to do it. Best Rgds, Dave R. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Cell formulas & formatting | kungfauxn00b | Word Tables | 3 | 04-05-2011 04:54 AM |
Add a period of time to a referenced "Time" cell? | derelyth | Excel | 4 | 03-27-2011 04:31 PM |
How can I fill cell color starting from Cell D5 using Conditional formatting instead | Learner7 | Excel | 0 | 07-08-2010 05:50 AM |
Conditional formatting of cell, Excel 2003 | Scaffold | Excel | 2 | 05-01-2010 08:56 AM |
Cell formatting | Niko | Excel | 5 | 04-23-2009 12:05 AM |