Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-03-2011, 07:34 PM
PomDave PomDave is offline Cell formatting for time - Excel 2010 Windows Vista Cell formatting for time - Excel 2010 Office 2010 32bit
Advanced Beginner
Cell formatting for time - Excel 2010
 
Join Date: Sep 2011
Location: Australia
Posts: 30
PomDave is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 09-04-2011, 11:20 PM
Catalin.B Catalin.B is offline Cell formatting for time - Excel 2010 Windows Vista Cell formatting for time - Excel 2010 Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

you can find details here...
http://support.microsoft.com/kb/266691
Reply With Quote
  #3  
Old 09-05-2011, 02:19 AM
OTPM OTPM is offline Cell formatting for time - Excel 2010 Windows 7 32bit Cell formatting for time - Excel 2010 Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

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)
Reply With Quote
  #4  
Old 09-05-2011, 04:34 AM
PomDave PomDave is offline Cell formatting for time - Excel 2010 Windows Vista Cell formatting for time - Excel 2010 Office 2010 32bit
Advanced Beginner
Cell formatting for time - Excel 2010
 
Join Date: Sep 2011
Location: Australia
Posts: 30
PomDave is on a distinguished road
Default

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
Reply With Quote
  #5  
Old 09-05-2011, 05:30 AM
OTPM OTPM is offline Cell formatting for time - Excel 2010 Windows 7 32bit Cell formatting for time - Excel 2010 Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

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)
Reply With Quote
  #6  
Old 09-05-2011, 10:49 PM
PomDave PomDave is offline Cell formatting for time - Excel 2010 Windows Vista Cell formatting for time - Excel 2010 Office 2010 32bit
Advanced Beginner
Cell formatting for time - Excel 2010
 
Join Date: Sep 2011
Location: Australia
Posts: 30
PomDave is on a distinguished road
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cell formatting for time - Excel 2010 Cell formulas & formatting kungfauxn00b Word Tables 3 04-05-2011 04:54 AM
Cell formatting for time - Excel 2010 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
Cell formatting for time - Excel 2010 Conditional formatting of cell, Excel 2003 Scaffold Excel 2 05-01-2010 08:56 AM
Cell formatting for time - Excel 2010 Cell formatting Niko Excel 5 04-23-2009 12:05 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:03 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft