Is it possible to type a number e.g. 1115 and excel converts that to 11:15 am ? or say 1600 to 4:00 pm this would greatly increasethe speed that I can enter timesheets into spreadsheet. I know I can do so by using a colon but wondered if I could be a bit more lazy!
Try this ,
and change the cell format to time.
Please Login or Register to view this content.
Happy Computing ,
Xlbiznes.
To show your appreciation please click *
See this...
Quick Time Entry
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
or in an adjacent cell
=TEXT(A1,"00\:00")+0 formatted as hh:mm
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
This can be done with a macro too - right click the tab name and click 'View Code', then paste the following:
Be aware this would affect any number inputted onto the sheet though - but this can be amended to check for only a certain range of cells.Please Login or Register to view this content.
You'd also need to select the correct formatting from the cell format menu.
that code wont work
consider 1115 which is 11:15
1115/2400
or 1115/100/24 = 0.464583333 =11:09 which is the result of decimal time 11.15 hours
Oh yeah, total rookie error! Whoops.
the other way do it would be format the cell as 00\:00 then use text(a1,"00\:00) in subsequent calculations
Thanks for the formula. Unfortunately it doesn't calculate the hours worked correctly when I use it. - If a cell is left blank then an error occurs. I have attached the file I'm trying to work with. Any further help would be most appreciated!!
if the user leaves the cell "B22" blank , you could use and iferror() function to trap that but no calculations will be done and that would result in using the 0 value if you do something like this :
Or else you could do conditional formatting on these cells, so that they turn red if the cells are blankPlease Login or Register to view this content.
Thanks heaps I now have a much more user friendly worksheet.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks