Time format control

Hi everyone,

I have a time of 08:38 and would like to truncate the last minute by
using formatting to achieve 08:30. I currently do this using a formula
to remove the last minute. I have tried custom formatting of hh.m but
I still get 08:38. I have even tried hh.m"0" but get 08:380.

Any Ideas would be greatly appreciated.

Regards Brad
0
brad_6663 (4)
6/6/2004 10:35:57 PM
excel 39879 articles. 2 followers. Follow

4 Replies
556 Views

Similar Articles

[PageSpeed] 40

You can use this formula, it sounds as though you want round down to nearest
10th minute

=FLOOR(A1,1/144)

-- 


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

"Brad kennedy" <brad_6663@hotmail.com> wrote in message
news:d3a24820.0406061435.564d31fb@posting.google.com...
> Hi everyone,
>
> I have a time of 08:38 and would like to truncate the last minute by
> using formatting to achieve 08:30. I currently do this using a formula
> to remove the last minute. I have tried custom formatting of hh.m but
> I still get 08:38. I have even tried hh.m"0" but get 08:380.
>
> Any Ideas would be greatly appreciated.
>
> Regards Brad


0
terre081 (3244)
6/6/2004 11:50:40 PM
If it is nearest half hour use

=FLOOR(A1,1/48)




-- 


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

"Peo Sjoblom" <terre08@mvps.org> wrote in message
news:ugnOTECTEHA.1272@TK2MSFTNGP10.phx.gbl...
> You can use this formula, it sounds as though you want round down to
nearest
> 10th minute
>
> =FLOOR(A1,1/144)
>
> -- 
>
>
> For everyone's benefit keep the discussion in the newsgroup.
>
> Regards,
>
> Peo Sjoblom
>
> "Brad kennedy" <brad_6663@hotmail.com> wrote in message
> news:d3a24820.0406061435.564d31fb@posting.google.com...
> > Hi everyone,
> >
> > I have a time of 08:38 and would like to truncate the last minute by
> > using formatting to achieve 08:30. I currently do this using a formula
> > to remove the last minute. I have tried custom formatting of hh.m but
> > I still get 08:38. I have even tried hh.m"0" but get 08:380.
> >
> > Any Ideas would be greatly appreciated.
> >
> > Regards Brad
>
>


0
terre081 (3244)
6/7/2004 12:04:44 AM
Thanks Peo

The formula will be very handy and is different to how I currently do
this. The question still remains to see if this can be achieved by
using formatting rather than a formula. The reason for this is I wish
to keep the last right minute in the cell for other calculations but I
don't want to see it.

Also what do you mean by "For everyone's benefit keep the discussion
in the newsgroup".  I have answered a question in the past for another
member but this is my first post (question) so I don't know what I did
wrong.

Regards Brad
0
brad_6663 (4)
6/7/2004 10:46:46 PM
Brad, that's my signature, it was not meant for you, you did nothing wrong.
It is just that I don't want
private emails sent to me with excel attachments and follow ups, any follow
ups
should be posted in the NG/forum. Since you are the 2nd person reacting
maybe I should
change it to "no private emails thank you"  <g>
Anyway, thanks for the feedback. You could still use the full minutes, if
you use
a formula to round down values you can refer to the cell/value that is being
rounded.
AFAIK there is no way of not displaying the last minute, even if you format
it as h:m it will show

-- 


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

"Brad kennedy" <brad_6663@hotmail.com> wrote in message
news:d3a24820.0406071446.5d67d6eb@posting.google.com...
> Thanks Peo
>
> The formula will be very handy and is different to how I currently do
> this. The question still remains to see if this can be achieved by
> using formatting rather than a formula. The reason for this is I wish
> to keep the last right minute in the cell for other calculations but I
> don't want to see it.
>
> Also what do you mean by "For everyone's benefit keep the discussion
> in the newsgroup".  I have answered a question in the past for another
> member but this is my first post (question) so I don't know what I did
> wrong.
>
> Regards Brad


0
terre081 (3244)
6/8/2004 12:09:29 AM
Reply:

Similar Artilces:

Manually scrolling an Edit control
Hello again, I am currently trying to get my Edit control at the bottom of a CDialog to scroll, but not having much luck (I've tried sending the message VK_DOWN to it, and using sendmessage, and even tried the ScrollWindowEx command but thus far to no avail). Essentially I have a custom control at the top of my page which keeps the focus, thus when I hit the "U" or "V" keys (which I determine using a PreTranslateMessage) I would like to send the command to my Edit control "emailBody" to scroll Up or Down. So I guess the basic question is what message sh...

how to iterate all controls of a CDialog
Hello Is there a way I can iterate all labels (CStatic objects) in a dialog? Thanks Vaclav >Is there a way I can iterate all labels (CStatic objects) in a dialog? Use EnumChildWindows, and check for the class name "STATIC" (use GetClassName) of the window in the callback. Dave -- MVP VC++ FAQ: http://www.mvps.org/vcfaq V�clav Jedli�ka wrote: >Hello > >Is there a way I can iterate all labels (CStatic objects) in a dialog? CWnd::GetWindow will let you iterate over all child windows. Less conveniently, there's ::EnumChildWindows. As for iterating over just th...

Changed a form to have two tab control
Code was writtten on the original form, but form is still named the same, the only thing that has changed is the control for the form changed to tabbed control and then I moved the individual controls to the appropriate tabbed sheets. Having a problem with the cmd buttons exiting to a main form, so I am thinking that I need to change the syntax on the code properites somehow. The main form is called "POCheck", the individual tabs are called "PO Form" and "Checkbook Form". I am not sure how the snytax needs to refer to the tabs? Here is an example: ...

Not able to remove the indentation in the list control after removing the imagelist
Hi, Anyone can help me. In my application once I remove the imagelist from the list control, indentation or space exists in the list control. I tried with setting iImage and iIndent members of LVITEM structure to -1. But no luck. Thanks in Advance, Balu. You could try calling Detach() for the image list in the CListCtrl. For example: CListCtrl *pMyList; // Added image list using SetImageList() pMyList->GetImageList()->Detach(); http://msdn2.microsoft.com/en-us/library/b75edw6k.aspx Tom "balu" <balakrishnakk@gmail.com> wrote in message news:1158207742.071489...

Microsoft Access License 2007 Problem License Require ActiveX Control
I was running Access 2007 just fine and today I start it up and it has: You don't have the license required to use this ActiveX Control You tried to open a form containing an OLE object or an ActiveX control or you tried to create and ActiveX control. To obtain the appropriate license, contact the company that provides the license OLE object or ActiveX control. But it never tells me what control it is talking about and not matter how many times I click ok it just comes right back and I cannot use Access. Any help is appreciated. Thanks. Try reregistering DAO360.dll http://support.m...

Tabs Hidden in Format Cell Box
When I right click on a cell and hit Format Cell, the lower tabs in the box that comes up giving me the options for formatting are hidden. I can see the upper tabs but not the lower tabs, why is this? I've heard of this before and the 'problem' was that the user had selected a large DPI setting under Control Panel, Display Properties, Advanced, General. Setting to Normal fixed it. -- Jim "JDM" <JDM@discussions.microsoft.com> wrote in message news:7ED7FFE8-D18C-4021-8AF0-26B6C7F4B899@microsoft.com... | When I right click on a cell and hit Format Cell, the l...

Conditional formating #4
I want a macro that will hide the values in a cell if another cell is blank Name Cost Paid Balance Jane Doe $10.00 $10.00 John Doe 5.00 5.00 5.00 Using Excel 2000 If Name is Null or Blank Hide Balance. If Name is not null show balance. Tonie Torie, Use Format>Conditional Formatting and set the dont colour to white. The formula would be =$A1="" -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Tonie Crawford" &l...

Mail Merge and Access format of telephone number
I use mail merge for a directory using Access for my data. The telephone numbers that are formatted in Access (555) 642-5555 aren't retained but come in to merge as 555642555 so I have to go to each one and put in the (xxx) 666-666. Is there anything I can do to retain the Access format of the tele numbers??? -- Terry50 See http://www.gmayor.com/formatting_word_fields.htm. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "terry50" <terry50@discussions.microsoft.com> wrote in message news:12784D...

merging values to word
i am merging numbers to word documents and the formatting will not stay. e.g. 45698723.2365879 is what shows up in my merge, however in excel i have formatted it so it appears as $45,698,723.24 can anyone help on this issue? ...

how to totalize only column value from certain accounting -> Currency format
For a sheet with invoices I need to totalize column values from a different currency (cell format Accounting). How to perform? All cells can have any currency USD or EUR. Column A Column B: USD 5,000.00 EUR 3,765,00 USD 100.00 Total USD 5,100.00 Total EUR 3,765.00 Thank you, Bart Excel 2003 =SUMPRODUCT(--(A1:A3="USD"),(B1:B3)) similar for euros -- Gary''s Student - gsnu2007c "AA Arens" wrote: > For a sheet with invoices I need to totalize column values from a > different currency (cell...

Bring cell format with contents?
(Sort of a novice, so try to keep it at my level, if possible) In my spreadsheet, I am comparing calculated results to an "acceptance value." If the results meet the "acceptance value" I want the cell to display "Passed." If not, I want the cell to display "Failed." Using the IF() function, I have it working (see below). What I would really like -- and this is just fluff -- is to have a cell with the text "Passed" in green and a cell with the text "Failed" in red. So if the IF statement is TRUE, I want the cell to display &...

OWA time out ?
Can the OWA client be configured to time out due to inactivity? Also, how can the default setting of OWA be configure for all clients access ? Thanks, Andrew If you're talking OWA 2003, then yes, use forms-based authentication: http://hellomate.typepad.com/exchange/2003/11/formsbased_auth.html -- Neil Hobson Exchange MVP For Exchange news, links and tips, check: http://www.msexchange.co.uk "andrewlin@canada.com" <anonymous@discussions.microsoft.com> wrote in message news:04b601c3c966$e2915b90$a001280a@phx.gbl... > Can the OWA client be configured to time out d...

An uneven time-increment
Dear all, If in a spreadsheet you have years 1985, 1986, 1987, 1988, 1989, 1990, 2050 with associated data for each year, how do you produce a line graph of this without 2050 being positioned at the same interval on the x-axis as the difference between the other years? To clarify, because the difference between 2050 and 1990 is greater than the difference between the previous years (all 1), how do get Excel to represent this on the x-axis of a line graph? Thanks for your help, Steve -- smurray444 ------------------------------------------------------------------------ smurray444's Pr...

Why is this combo box giving me hard time?
i load my combo box with data in the ressource editor . data: 0;1;2;3. But when i lunch the app. nothing in my Cono box. The combo box is on a dialog box.. Bredal Jensen wrote: > i load my combo box with data in the ressource editor . > > data: 0;1;2;3. > > But when i lunch the app. nothing in my Cono box. > > The combo box is on a dialog box.. > > Are you sure there is nothing? You must call oCombo.SetSel(0); Otherwise the first item is blank. BTW, don't use the editor. Why don;t you rather do oCombo.AddString("0"); oCombo.AddString("...

Font Color Conditional Formatting/Color Scaling
tWithin a matrix of data, I want to use two conditional formating/color scaling: one for the background color of the cell, the second for the font color corresponding to a different (but same size) data matrix. I know how to use the conditional formatting/color scaling for the background color--however would it be possible to have a different color scaling for the font color (and applied to one data matrix but referencing a separate data matrix)? Or even, can I at least use the conditional formatting/color scaling but changing the font color instead of the background cell col...

Why can't I retain sig formatting on replies?
I�ve created an email signature in Word which contains various tex formatting and an image, but when I reply to email messages, m signature reverts to plain text with no image. This seems to happe only on replies. Why? I�m using Outlook 2003. FYI, I�ve got th signature set for New Messages as well as Replies & Forwards ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Well, I just checked, and it works for some replies, but not others?? --------------------------------...

Last logon time error
I'm using Exch 5.5 . In the server admin page the section server > Private Information stores > mailbox Resources , some user last logon time showing fictitious date .When I check the system date of that machine it showing the correct date , also last access time in the LOGON section showing the correct date / time ...

How to copy custom number formats
Hello All I have a custom number format for a Excel Cell A1 which is #,##0.00 "CAD" and in A1 I now have a value 1000 CAD. I have assigned cell B1 as =A1 but an external application when the report is run populated now cell A1 as 20000 MXP and now my cell B1 says 20000 CAD and not 20000 MXP and when I check the format for B1 it still as the format as #,##0.00 "CAD" whereas now the format of my cell A1 is #,##0.00 "MXP" appears only the value and not the format is being copied. Can any of you suggest a workaround to resolve this problem. Many thanks Simon ...

Cell Formats in formulas
I have a worksheet set up with formulas to automatically copy values from another cell as it is changed. Is there a way to include the format of the reference cell (font, color) when referencing it in a formula. Example: when I change to a different symbol font in the referenced cell, the referencing cells only change the value, not the font, so I don't end up with the correct symbols in the referencing cell. No, it's not possible to do that using formulas, only format formulas can do are number formats and only using the text function Regards, Peo Sjoblom "C. Lewis"...

Limited FREE BOOK (e-book format) and DVD giveaway
Get Jamie's Newly Revised FREE BOOK (e-book format) and DVD (valued at $97.00) As a special gift, we would like to provide you with a Free copy of Jamie McIntyre's newly revised E-Book and DVD (In hard copy mailed to you) titled: "What I Didn't Learn At School But Wish I Had" The key topics covered in this Book and DVD are: Jamie McIntyre Click Here For Your Free DVD! http://www.21stcenturyacademy.com.au/cmd.php?af=262381 " What you should have been taught at school, but weren't. " The 4 key skills you must master to succeed in the 21st Cent...

Use Spinner Control to Hide/Show Buttons?
Hi All, I am trying to set up a spinner button (with corresponding numerical readout) that also displays a number of buttons. The number of buttons displayed will depend upon the spinner number e.g. if the spinner says 5, then 5 buttons will be displayed. If the spinner says 2, then the rest of the buttons apart from 2 are hidden. There needs to be 10 buttons in total, with a minimum of two displayed at any one time. I've created a mock-up worksheet to show what I mean MUCH better than the explanation I've just given above! http://homepages.nildram.co.uk/~proebuck/Buttons.xls So I...

Changing formatting on bar charts
I have a bar chart that shows turn around times for various jobs our group has in process. The chart shows the customer name @ the bottom, # of days up the side then the bars are for all the jobs each customer has. Here are my problems/things I want to change: 1) the spacing for each customer is equal whether they have 1 job or 10, can that be adjusted as needed? 2) My bars are so narrow that you can barely see the color because of the customer spacing issue mentioned above(I assume). How can I change the width of these bars? ...

Delegat Control
Running Exchange 2003 on windows 2003 standard edition. Can anyone tell me how I go about allowing someone else on staff to be able to edit contact information (formerly recipients in Exchange 5.5) on our distribution lists? I right-clicked the contacts folder in the recipients container in Exchange then delegate control and selected the user. But when she opens the address book in Outlook she can only view the information and can't add/delete or make changes to the contacts. I assume I need to tweak permissions. But where? Appreciate any info. Thanks. Zach Hi Zach, Unfortunatly u...

conditional formatting
I have used conditional formatting to highlight some issues in a spreadsheet. However, to distribute this, I need to remove the link to the base data used as the source file is too big. Is there a way I can copy the cell shading so that the condition is removed but the formatting remains? I am using 2003, and the spreadsheet is 7500 rows x 70 columns and running on a laptop, so any solution can't be too memory or processor intensive! Thanks for your suggestions Take a look at http://xldynamic.com/source/xld.CFConditions.html and see if it helps, although it is not si...

date+time input mask
Hi, I have a textbox where I'd like users to input date and time in the format: mm/dd/yyyy hh:mm AM (or PM). My input mask is: 00/00/0000\ 00:00\ >LL The format is General Date. The column this textbox is bound to on the table is date/time with format of General Date. When I input a date and time, I'm getting an error. I input 03/31/2010 01: 15 AM. When I tab off the field, I get the following message: "The value you entered isn't valid for this field". What am I doing wrong? thanks. CLO -- Message posted via AccessMonster.com ht...