Date Formatting #8

Hi Folks - I like to display my dates as MMM-YY. Also, I like to ENTER my 
dates as MMM-YY. The problem is that if I enter Feb-07 (for me, February 
2007), Excel treats that as February 7th, 2009. Is there anyway I can force 
Excel to recognize my date entry as MMM-YY?

Thanks,

Michael


0
info3116 (20)
2/23/2009 5:50:54 PM
excel 39879 articles. 2 followers. Follow

12 Replies
942 Views

Similar Articles

[PageSpeed] 7

First off, Feb-07 is not a date... a date contains a month, DAY and year. 
You can force Excel to treat Feb-07 as a date by supplying a day value. If 
you don't want to type it in, then you will need some VB event code to force 
the conversion to an actual date. Is a VB solution acceptable?

-- 
Rick (MVP - Excel)


"Michael" <info@homekeyinc.com> wrote in message 
news:2mBol.21966$rb1.15798@newsfe02.iad...
> Hi Folks - I like to display my dates as MMM-YY. Also, I like to ENTER my 
> dates as MMM-YY. The problem is that if I enter Feb-07 (for me, February 
> 2007), Excel treats that as February 7th, 2009. Is there anyway I can 
> force Excel to recognize my date entry as MMM-YY?
>
> Thanks,
>
> Michael
>
> 

0
2/23/2009 6:23:43 PM
Understood ... However, when I type Feb-07, Excel THINKS it is a date and 
converts to a date format of DD-MMM. I was hoping to intercept that logic ON 
ENTER and convert to MMM-YY. I guess that is where the VBA comes into play. 
I'll just get used to entering the full date. Thnaks.

Michael




"Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
news:uR8WqPelJHA.1168@TK2MSFTNGP05.phx.gbl...
> First off, Feb-07 is not a date... a date contains a month, DAY and year. 
> You can force Excel to treat Feb-07 as a date by supplying a day value. If 
> you don't want to type it in, then you will need some VB event code to 
> force the conversion to an actual date. Is a VB solution acceptable?
>
> -- 
> Rick (MVP - Excel)
>
>
> "Michael" <info@homekeyinc.com> wrote in message 
> news:2mBol.21966$rb1.15798@newsfe02.iad...
>> Hi Folks - I like to display my dates as MMM-YY. Also, I like to ENTER my 
>> dates as MMM-YY. The problem is that if I enter Feb-07 (for me, February 
>> 2007), Excel treats that as February 7th, 2009. Is there anyway I can 
>> force Excel to recognize my date entry as MMM-YY?
>>
>> Thanks,
>>
>> Michael
>>
>>
> 


0
mll1 (3)
2/23/2009 9:28:33 PM
Correct... VBA would be needed to incept the Feb-07 entry and change to a 
date that you could format the way you want BEFORE Excel had a chance to 
mangle it in a way you didn't want.

-- 
Rick (MVP - Excel)


"Michael" <mll@homekeyinc.com> wrote in message 
news:QxEol.23959$EO2.4893@newsfe04.iad...
> Understood ... However, when I type Feb-07, Excel THINKS it is a date and 
> converts to a date format of DD-MMM. I was hoping to intercept that logic 
> ON ENTER and convert to MMM-YY. I guess that is where the VBA comes into 
> play. I'll just get used to entering the full date. Thnaks.
>
> Michael
>
>
>
>
> "Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
> news:uR8WqPelJHA.1168@TK2MSFTNGP05.phx.gbl...
>> First off, Feb-07 is not a date... a date contains a month, DAY and year. 
>> You can force Excel to treat Feb-07 as a date by supplying a day value. 
>> If you don't want to type it in, then you will need some VB event code to 
>> force the conversion to an actual date. Is a VB solution acceptable?
>>
>> -- 
>> Rick (MVP - Excel)
>>
>>
>> "Michael" <info@homekeyinc.com> wrote in message 
>> news:2mBol.21966$rb1.15798@newsfe02.iad...
>>> Hi Folks - I like to display my dates as MMM-YY. Also, I like to ENTER 
>>> my dates as MMM-YY. The problem is that if I enter Feb-07 (for me, 
>>> February 2007), Excel treats that as February 7th, 2009. Is there anyway 
>>> I can force Excel to recognize my date entry as MMM-YY?
>>>
>>> Thanks,
>>>
>>> Michael
>>>
>>>
>>
>
> 

0
2/23/2009 10:03:04 PM
But remember that excel will make it a real date (if it sees it as a date)
before any event code will fire.

You may want to either enter the values as real dates or format your input range
as Text and have the event macro parse the value to what you want.

Michael wrote:
> 
> Understood ... However, when I type Feb-07, Excel THINKS it is a date and
> converts to a date format of DD-MMM. I was hoping to intercept that logic ON
> ENTER and convert to MMM-YY. I guess that is where the VBA comes into play.
> I'll just get used to entering the full date. Thnaks.
> 
> Michael
> 
> "Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message
> news:uR8WqPelJHA.1168@TK2MSFTNGP05.phx.gbl...
> > First off, Feb-07 is not a date... a date contains a month, DAY and year.
> > You can force Excel to treat Feb-07 as a date by supplying a day value. If
> > you don't want to type it in, then you will need some VB event code to
> > force the conversion to an actual date. Is a VB solution acceptable?
> >
> > --
> > Rick (MVP - Excel)
> >
> >
> > "Michael" <info@homekeyinc.com> wrote in message
> > news:2mBol.21966$rb1.15798@newsfe02.iad...
> >> Hi Folks - I like to display my dates as MMM-YY. Also, I like to ENTER my
> >> dates as MMM-YY. The problem is that if I enter Feb-07 (for me, February
> >> 2007), Excel treats that as February 7th, 2009. Is there anyway I can
> >> force Excel to recognize my date entry as MMM-YY?
> >>
> >> Thanks,
> >>
> >> Michael
> >>
> >>
> >

-- 

Dave Peterson
0
petersod (12005)
2/23/2009 10:50:42 PM
I was thinking of formatting the cell as text via code in SelectionChange 
event (to handle that problem) and then reformatting it in the Change event 
once I had changed the entry to one that would satisfy the OP's requirement.

-- 
Rick (MVP - Excel)


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:49A32842.997C5AB3@verizonXSPAM.net...
> But remember that excel will make it a real date (if it sees it as a date)
> before any event code will fire.
>
> You may want to either enter the values as real dates or format your input 
> range
> as Text and have the event macro parse the value to what you want.
>
> Michael wrote:
>>
>> Understood ... However, when I type Feb-07, Excel THINKS it is a date and
>> converts to a date format of DD-MMM. I was hoping to intercept that logic 
>> ON
>> ENTER and convert to MMM-YY. I guess that is where the VBA comes into 
>> play.
>> I'll just get used to entering the full date. Thnaks.
>>
>> Michael
>>
>> "Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message
>> news:uR8WqPelJHA.1168@TK2MSFTNGP05.phx.gbl...
>> > First off, Feb-07 is not a date... a date contains a month, DAY and 
>> > year.
>> > You can force Excel to treat Feb-07 as a date by supplying a day value. 
>> > If
>> > you don't want to type it in, then you will need some VB event code to
>> > force the conversion to an actual date. Is a VB solution acceptable?
>> >
>> > --
>> > Rick (MVP - Excel)
>> >
>> >
>> > "Michael" <info@homekeyinc.com> wrote in message
>> > news:2mBol.21966$rb1.15798@newsfe02.iad...
>> >> Hi Folks - I like to display my dates as MMM-YY. Also, I like to ENTER 
>> >> my
>> >> dates as MMM-YY. The problem is that if I enter Feb-07 (for me, 
>> >> February
>> >> 2007), Excel treats that as February 7th, 2009. Is there anyway I can
>> >> force Excel to recognize my date entry as MMM-YY?
>> >>
>> >> Thanks,
>> >>
>> >> Michael
>> >>
>> >>
>> >
>
> -- 
>
> Dave Peterson 

0
2/24/2009 12:00:49 AM
Hi,

Actually, Feb-07 is considered a date by Excel.  It automatically assumes 
the current year and makes your entry Feb 7 2009 or in Excel's style 2/7/09.

Here is one workaround - enter Feb-2007 that will be treated as 2/1/2007.

On the other hand if you really don't want a date here is the code:

Private Sub Worksheet_Change(ByVal Target As Range) 
    Dim isect As Range
    Set isect = Application.Intersect(Target, Range("A1:A10"))
    If Not isect Is Nothing Then
       On Error GoTo ErrorHandler
       Application.EnableEvents = False
       Target = DateValue("" & Month(Target) & "-1-" & Day(Target) & "")
       Target.NumberFormat = "MMM-YY"
    End If
ErrorHandler:
    Application.EnableEvents = True
End Sub

1. To add this code to your file, press Alt+F11,  
2.  In the VBAProject window, top left side, find your sheet name under your 
file name and double click it.
3.  Paste in or type the code above. 
-- 
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Michael" wrote:

> Hi Folks - I like to display my dates as MMM-YY. Also, I like to ENTER my 
> dates as MMM-YY. The problem is that if I enter Feb-07 (for me, February 
> 2007), Excel treats that as February 7th, 2009. Is there anyway I can force 
> Excel to recognize my date entry as MMM-YY?
> 
> Thanks,
> 
> Michael
> 
> 
> 
0
2/24/2009 12:50:03 AM
One thing that may speak against using your event code... enter Feb-07 into 
the cell, hit Enter, re-select the cell, click into the formula bar and then 
hit the Enter key.

I was thinking of event code along these lines (which is immune to the above 
problem)...

'*************** START OF CODE ***************
Dim OldFormat As String
Dim OldAddress As String

Private Sub Worksheet_Change(ByVal Target As Range)
  With Target
    If Not Intersect(Target, Range("A1:A9")) Is Nothing And .Count = 1 Then
      Application.EnableEvents = False
      On Error GoTo Whoops
      If IsDate("28-" & .Value) Then
        .NumberFormat = "mmm-yy"
        .Value = CDate("28-" & .Value) - 27
        OldFormat = "mmm-yy"
      ElseIf IsDate(.Value) Then
        .NumberFormat = "mmm-yy"
        .Value = CDate(.Value)
        OldFormat = "mmm-yy"
      End If
    End If
  End With
Whoops:
  Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If OldAddress <> "" Then Range(OldAddress).NumberFormat = OldFormat
  OldAddress = Target.Address
  OldFormat = Target.NumberFormat
  If Not Intersect(Target, Range("A1:A9")) Is Nothing Then
    Target.NumberFormat = "@"
  End If
End Sub
'*************** END OF CODE ***************

The only anomaly I see with this code is that clicking back into a cell in 
the target range converts the entry to its serial number value until you 
leave the cell or change its contents.

-- 
Rick (MVP - Excel)


"Shane Devenshire" <ShaneDevenshire@discussions.microsoft.com> wrote in
message news:3A8193ED-03C1-4D86-AD7E-CC645E04E1D9@microsoft.com...
> Hi,
>
> Actually, Feb-07 is considered a date by Excel.  It automatically assumes
> the current year and makes your entry Feb 7 2009 or in Excel's style
> 2/7/09.
>
> Here is one workaround - enter Feb-2007 that will be treated as 2/1/2007.
>
> On the other hand if you really don't want a date here is the code:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>    Dim isect As Range
>    Set isect = Application.Intersect(Target, Range("A1:A10"))
>    If Not isect Is Nothing Then
>       On Error GoTo ErrorHandler
>       Application.EnableEvents = False
>       Target = DateValue("" & Month(Target) & "-1-" & Day(Target) & "")
>       Target.NumberFormat = "MMM-YY"
>    End If
> ErrorHandler:
>    Application.EnableEvents = True
> End Sub
>
> 1. To add this code to your file, press Alt+F11,
> 2.  In the VBAProject window, top left side, find your sheet name under
> your
> file name and double click it.
> 3.  Paste in or type the code above.
> -- 
> If this helps, please click the Yes button
>
> Cheers,
> Shane Devenshire
>
>
> "Michael" wrote:
>
>> Hi Folks - I like to display my dates as MMM-YY. Also, I like to ENTER my
>> dates as MMM-YY. The problem is that if I enter Feb-07 (for me, February
>> 2007), Excel treats that as February 7th, 2009. Is there anyway I can
>> force
>> Excel to recognize my date entry as MMM-YY?
>>
>> Thanks,
>>
>> Michael
>>
>>
>>

0
2/24/2009 2:26:45 AM
There are some things that seem too much like magic for me.  I wouldn't use an
event.  I'd learn to enter the value as a real date.  

That may not be what the OP wants, though <vbg>.

Rick Rothstein wrote:
> 
> I was thinking of formatting the cell as text via code in SelectionChange
> event (to handle that problem) and then reformatting it in the Change event
> once I had changed the entry to one that would satisfy the OP's requirement.
> 
> --
> Rick (MVP - Excel)
> 
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:49A32842.997C5AB3@verizonXSPAM.net...
> > But remember that excel will make it a real date (if it sees it as a date)
> > before any event code will fire.
> >
> > You may want to either enter the values as real dates or format your input
> > range
> > as Text and have the event macro parse the value to what you want.
> >
> > Michael wrote:
> >>
> >> Understood ... However, when I type Feb-07, Excel THINKS it is a date and
> >> converts to a date format of DD-MMM. I was hoping to intercept that logic
> >> ON
> >> ENTER and convert to MMM-YY. I guess that is where the VBA comes into
> >> play.
> >> I'll just get used to entering the full date. Thnaks.
> >>
> >> Michael
> >>
> >> "Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message
> >> news:uR8WqPelJHA.1168@TK2MSFTNGP05.phx.gbl...
> >> > First off, Feb-07 is not a date... a date contains a month, DAY and
> >> > year.
> >> > You can force Excel to treat Feb-07 as a date by supplying a day value.
> >> > If
> >> > you don't want to type it in, then you will need some VB event code to
> >> > force the conversion to an actual date. Is a VB solution acceptable?
> >> >
> >> > --
> >> > Rick (MVP - Excel)
> >> >
> >> >
> >> > "Michael" <info@homekeyinc.com> wrote in message
> >> > news:2mBol.21966$rb1.15798@newsfe02.iad...
> >> >> Hi Folks - I like to display my dates as MMM-YY. Also, I like to ENTER
> >> >> my
> >> >> dates as MMM-YY. The problem is that if I enter Feb-07 (for me,
> >> >> February
> >> >> 2007), Excel treats that as February 7th, 2009. Is there anyway I can
> >> >> force Excel to recognize my date entry as MMM-YY?
> >> >>
> >> >> Thanks,
> >> >>
> >> >> Michael
> >> >>
> >> >>
> >> >
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
petersod (12005)
2/24/2009 2:37:33 AM
I posted the code I was thinking about in response to Shane's posting if you 
are interested in seeing what I had in mind. By the way, I agree with you on 
just entering a correct date to begin with (just two additional characters 
would do it).

-- 
Rick (MVP - Excel)


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:49A35D6D.2F621184@verizonXSPAM.net...
> There are some things that seem too much like magic for me.  I wouldn't 
> use an
> event.  I'd learn to enter the value as a real date.
>
> That may not be what the OP wants, though <vbg>.
>
> Rick Rothstein wrote:
>>
>> I was thinking of formatting the cell as text via code in SelectionChange
>> event (to handle that problem) and then reformatting it in the Change 
>> event
>> once I had changed the entry to one that would satisfy the OP's 
>> requirement.
>>
>> --
>> Rick (MVP - Excel)
>>
>> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>> news:49A32842.997C5AB3@verizonXSPAM.net...
>> > But remember that excel will make it a real date (if it sees it as a 
>> > date)
>> > before any event code will fire.
>> >
>> > You may want to either enter the values as real dates or format your 
>> > input
>> > range
>> > as Text and have the event macro parse the value to what you want.
>> >
>> > Michael wrote:
>> >>
>> >> Understood ... However, when I type Feb-07, Excel THINKS it is a date 
>> >> and
>> >> converts to a date format of DD-MMM. I was hoping to intercept that 
>> >> logic
>> >> ON
>> >> ENTER and convert to MMM-YY. I guess that is where the VBA comes into
>> >> play.
>> >> I'll just get used to entering the full date. Thnaks.
>> >>
>> >> Michael
>> >>
>> >> "Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in 
>> >> message
>> >> news:uR8WqPelJHA.1168@TK2MSFTNGP05.phx.gbl...
>> >> > First off, Feb-07 is not a date... a date contains a month, DAY and
>> >> > year.
>> >> > You can force Excel to treat Feb-07 as a date by supplying a day 
>> >> > value.
>> >> > If
>> >> > you don't want to type it in, then you will need some VB event code 
>> >> > to
>> >> > force the conversion to an actual date. Is a VB solution acceptable?
>> >> >
>> >> > --
>> >> > Rick (MVP - Excel)
>> >> >
>> >> >
>> >> > "Michael" <info@homekeyinc.com> wrote in message
>> >> > news:2mBol.21966$rb1.15798@newsfe02.iad...
>> >> >> Hi Folks - I like to display my dates as MMM-YY. Also, I like to 
>> >> >> ENTER
>> >> >> my
>> >> >> dates as MMM-YY. The problem is that if I enter Feb-07 (for me,
>> >> >> February
>> >> >> 2007), Excel treats that as February 7th, 2009. Is there anyway I 
>> >> >> can
>> >> >> force Excel to recognize my date entry as MMM-YY?
>> >> >>
>> >> >> Thanks,
>> >> >>
>> >> >> Michael
>> >> >>
>> >> >>
>> >> >
>> >
>> > --
>> >
>> > Dave Peterson
>
> -- 
>
> Dave Peterson 

0
2/24/2009 2:53:11 AM
All that to save 2 characters of typing doesn't seem worth it to me.

And event macros have that clearing the clipboard problem that can make it a
nonstarter for lots of things.



Rick Rothstein wrote:
> 
> I posted the code I was thinking about in response to Shane's posting if you
> are interested in seeing what I had in mind. By the way, I agree with you on
> just entering a correct date to begin with (just two additional characters
> would do it).
> 
> --
> Rick (MVP - Excel)
> 
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:49A35D6D.2F621184@verizonXSPAM.net...
> > There are some things that seem too much like magic for me.  I wouldn't
> > use an
> > event.  I'd learn to enter the value as a real date.
> >
> > That may not be what the OP wants, though <vbg>.
> >
> > Rick Rothstein wrote:
> >>
> >> I was thinking of formatting the cell as text via code in SelectionChange
> >> event (to handle that problem) and then reformatting it in the Change
> >> event
> >> once I had changed the entry to one that would satisfy the OP's
> >> requirement.
> >>
> >> --
> >> Rick (MVP - Excel)
> >>
> >> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> >> news:49A32842.997C5AB3@verizonXSPAM.net...
> >> > But remember that excel will make it a real date (if it sees it as a
> >> > date)
> >> > before any event code will fire.
> >> >
> >> > You may want to either enter the values as real dates or format your
> >> > input
> >> > range
> >> > as Text and have the event macro parse the value to what you want.
> >> >
> >> > Michael wrote:
> >> >>
> >> >> Understood ... However, when I type Feb-07, Excel THINKS it is a date
> >> >> and
> >> >> converts to a date format of DD-MMM. I was hoping to intercept that
> >> >> logic
> >> >> ON
> >> >> ENTER and convert to MMM-YY. I guess that is where the VBA comes into
> >> >> play.
> >> >> I'll just get used to entering the full date. Thnaks.
> >> >>
> >> >> Michael
> >> >>
> >> >> "Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
> >> >> message
> >> >> news:uR8WqPelJHA.1168@TK2MSFTNGP05.phx.gbl...
> >> >> > First off, Feb-07 is not a date... a date contains a month, DAY and
> >> >> > year.
> >> >> > You can force Excel to treat Feb-07 as a date by supplying a day
> >> >> > value.
> >> >> > If
> >> >> > you don't want to type it in, then you will need some VB event code
> >> >> > to
> >> >> > force the conversion to an actual date. Is a VB solution acceptable?
> >> >> >
> >> >> > --
> >> >> > Rick (MVP - Excel)
> >> >> >
> >> >> >
> >> >> > "Michael" <info@homekeyinc.com> wrote in message
> >> >> > news:2mBol.21966$rb1.15798@newsfe02.iad...
> >> >> >> Hi Folks - I like to display my dates as MMM-YY. Also, I like to
> >> >> >> ENTER
> >> >> >> my
> >> >> >> dates as MMM-YY. The problem is that if I enter Feb-07 (for me,
> >> >> >> February
> >> >> >> 2007), Excel treats that as February 7th, 2009. Is there anyway I
> >> >> >> can
> >> >> >> force Excel to recognize my date entry as MMM-YY?
> >> >> >>
> >> >> >> Thanks,
> >> >> >>
> >> >> >> Michael
> >> >> >>
> >> >> >>
> >> >> >
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
petersod (12005)
2/24/2009 3:03:31 AM
> All that to save 2 characters of typing doesn't seem worth it to me.

No argument from me on that one.

> And event macros have that clearing the clipboard problem that can make it 
> a
> nonstarter for lots of things.

I haven't looked into it, but I would guess the clipboard could be protected 
(well, copied and then replaced) using some API function calls. I'll have to 
look into that idea one of these days.

-- 
Rick (MVP - Excel)

0
2/24/2009 3:15:25 AM
Thanks folks - I think I'll just get used to entering the full date. 
Interesting thoughts on the subject, though.

Michael



"Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
news:OA4904ilJHA.4912@TK2MSFTNGP04.phx.gbl...
>> All that to save 2 characters of typing doesn't seem worth it to me.
>
> No argument from me on that one.
>
>> And event macros have that clearing the clipboard problem that can make 
>> it a
>> nonstarter for lots of things.
>
> I haven't looked into it, but I would guess the clipboard could be 
> protected (well, copied and then replaced) using some API function calls. 
> I'll have to look into that idea one of these days.
>
> -- 
> Rick (MVP - Excel)
> 


0
mll1 (3)
2/24/2009 12:00:39 PM
Reply:

Similar Artilces:

How do I calculate escalation rates using dates and a set value
If I have a projected that needs to be escalated using start and completion dates by a certain percentage per year, how do I write the formula. For example if my project starts May 26, 2010 and lasts until may 26 2014 I want to add escalation to midpoint at 4% per year. ...

formula help #8
The following formula queries and totals information on a specified sheet. In this case the specified sheet name is 2003 I have several sheets (2001, 2002, 2003, 2004.....) I would like to have the value for the sheet name in the formula reference a cell in which I could type in the name of the sheet I want to query. Can someone tell me how to accomplish this. Just changing the '2003' in the formula to the reference cell doesn't work. (FYI - the reference cell will be L1) =SUMIF(('2003'!H2:I400),+(H3),('2003'!D2:I400)) TIA Mike =SUMIF((INDIRECT("'...

DTS Export fails on bad date
While trying to import an excel spreadsheet to a SQL Server table, DTS fails, pointing to a column with date values. I have looked at all the date values, and they appear correct. I need to get this excel spreadsheet loaded to a database table. Is there any way to pinpoint the exact cell causing the problem? Or, can you think of another way to export the data in the spreadsheet besides DTS that might not be so sensitive to data content? Thanks, Dean Slindee ...

Trouble with time formats
Any help appreciated! I need to work out the average speed it would take to cover a certain distance. EG. If you covered 10 miles in 28 minutes what would teh average speed be... Column A: 10 miles Column B: 28 minutes Column C: Would return 21.43mph. I then want to to add time increments of say 15 seconds in rows so that I can see that if it took 28 minutes, 45 seconds, the average speed column would return 20.87mph. I can work it out by entering 28.75 for the time (or 70.50 for 1h,10m,30s), but I want to format it as time, i.e: 00:28:45 (or 01:10:30) Is this possible? Thanks...

Copying sorksheet formatting
How do I copy worksheet formatting, inc. custom headers, footers, column & row formatting across all the worksheets in a workbook? Hi one way: formating them all at the same time: - group the sheets (hold down the SHIFT key while selecting the sheets) - apply the format to a cell, column, row -- Regards Frank Kabel Frankfurt, Germany driverdriver wrote: > How do I copy worksheet formatting, inc. custom headers, > footers, column & row formatting across all the worksheets > in a workbook? You can also select the entire sheet with CTRL+A and do Edit / Copy, then group yo...

disc date for next month
We want to be able to choose a discount date for the next month the discount is allowed. Our terms are 2% 10th net 15th. We can choose the due date for the 15th of the next month, but we can't choose the discount date as the 10th of the next month. All the invoices entered into the system from the 1st thru the 10th calculate the discount for the current month instead of the next month. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the me...

combine text & date formula to show mmmm dd, yyyy?
How do I combine text and date formula to show the written date and not the Excel number date? I have text in a couple of cells and a date formula in another cell(input from a different worksheet). I have the formula =A3 & F3 & A4 in A1 and would like it to read: We went to the station on December 21, 2009 and took the train. However, it is reading We went to the station on 40168 and took the train. Thanks. Try something like this... A1 = Today is B1 = 12/21/2009 =A1&" "&TEXT(B1,"mmmm dd, yyyy") Returns: Today is December 21, 2009 ...

Can I use 'or' in conditional formatting?
I'd like to have 4 conditions, Red, Yellow, and Green. But I have two conditions for green, without using two conditions for green is there a way to use OR somehow to beat the limit? Thanks, Norm PS Win2000 + XL2002 Sure ... just click on "Formula Is", and you can use "OR" for more then two conditions. For example: =OR(A1=2,A1=4,A1="good",A1="bad") Will trigger the set format if "any" of the above equate to "True". -- HTH, RD --------------------------------------------------------------------------- Please keep all corr...

Is there a way to copy and paste the date into worksheets
Is there a way to copy and paste dates into the worksheet withou individually typing up each one -- mark_vi ----------------------------------------------------------------------- mark_vi_'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2450 View this thread: http://www.excelforum.com/showthread.php?threadid=38137 Is there a way to insert it the date into the the worksheet tab (not th cells in the actual worksheet but the name of the worksheet itself -- mark_vi ----------------------------------------------------------------------- mark_vi_'s Profile: ht...

Excel's column width format box
Does anyone know why I am unable to enter a number into Excel's column width box, yet using the mouse, I am able to adjust the width. When opening the text box, I can delete the number that appears, but am unable to even reenter that one after it is deleted. I must tell you that I'm a new user who's working through numerous tutorials. Thanks, Hi do you get an error message or what happens exactly -- Regards Frank Kabel Frankfurt, Germany rly2rys wrote: > Does anyone know why I am unable to enter a number into Excel's > column width box, yet using the mouse, I am ...

Formaula for less than date and name begins with...!!! HELP!!!
IM trying to create a formula that gives me a total count for cells that are less than 02/05/10 and the name begins with FHLM, heres what i came up with... =COUNTIF(E:E,">="&DATE(1900,1,1))-COUNTIF(E:E,">="&DATE(2010,2,5))+COUNTIF(E:E,DATE(2010,2,5))-COUNTIF(U:U,"FHLM*") I keep gettin a bunch of ###### or 0, depending on how I move the data around.. Any ideas???? =SUMPRODUCT(--(E1:E100<=DATE(2010,2,5)),--(LEFT(U1:U100,4)="FHLM")) "Senor Martinez" wrote: > IM trying to create a formula that gives me...

Help! Search & Replacing time formats
Hi I have a whole spreadsheet full of fields similiar to the following 10:00AM 12:15PM 2:25PM 4:40PM 7:00PM 9:20PM 10:00PM 12:20PM 2:25PM 4:30PM 7:15PM 10:00AM 12:30PM 2:45PM 9:15PM 10:40AM 1:10PM 5:00PM 7:20PM 9:35PM etc. I need to convert all times to 24 hour, and drop the AM & PM. The later is easy, but how do I do a quick Search & Replace without incorrectly converting 10:**AM to 22:** etc. Any tips would be appreciated. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly fro...

Excel 2003 not recognizing whole dollar amounts, formats amt as ce
In my earlier version of Excel, (Office 97), I could format the cells to automatically enter amounts as dollars and cents. For example, I would type 30. and the amount was shown as $30.00. Or, I could type 4357, and the amount was shown as $43.57. In the Office 2003 version if I type 30., the amount is reflected in the cell as 30 cents instead of $30.00. Yes that is a change that other people have complained about when one uses fixed decimals -- Regards, Peo Sjoblom (No private emails please) "hat1" <hat1@discussions.microsoft.com> wrote in message news:2C3CBB7C...

Formatting data table
I have a data table shown under a diagram. However it won't display all the decimals. I.e. in the input to the diagram for january 2006 is 1.123, but only 1.1 is shown in the data table. The only solution seems to be to enlarge the diagram but this is not an option in this particular case! As a last resort I could embed a table under a traditional diagram without the data table, but that is second best. Looking forward to any help If you right click on the data table you can change the font size by clicking on 'format data table' and the font tab. You can type in the size ...

Grouping dates in pivot table
I am pulling data from a SQL Server database to create a pivot table. Excel (07) is not recognizing the field as a date. This is a field I would like to group by in the pivottable. I am aware of all the techniques to convert this to a date field, but I am searching for an answer as to why XL pivot tables cannot consume the dates directly from a sql query. I have played with bringing the dates back in a number of differenent formats with no success. Any insights into this would be appreciated. Probably coming in as text and yuo may only need to copy an unused cell and paste sp...

how do i change dates on my calendar and keep my pics
Publisher doesn't support this. You would need to do it manually. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com ~pay it forward~ This posting is provided "AS IS" with no warranties, and confers no rights. "Bob T" <Bob T@discussions.microsoft.com> wrote in message news:68671977-F71B-444F-AE72-801D2B359FEE@microsoft.com... > ...

Conditional formatting
Is it possible to shade fields progressively darker or lighter depending on the values. For example, I would like the following values to have the following colours: -5 dark red -4 lighter red -3 slightly lighter red -2 etc, etc 1 light green 5 dark green One step further - would it be possible to tell excel to work out which is the lowest and highest number in the data set and work out for itself how light or dark to shade the values e.g, if my dataset happened to have the values -50, -40, etc excel would work out which was a low value and which was a high one. Conditional formatting ...

Conditional Format Help Needed
I would like to apply a Conditional Format based on two factors. Currently I have two different Conditions set up. Condition 1: =IF(IF($G2="Ready to Publish",1,0)=1,1,0) This format successfully works to gray out and cross out text that matches "Ready to Publish." Condition 2: =MOD(ROW()-1,2)=0 This format applies alternating row fills for easier viewing. While both of these formulas work, my problem is Condition 1 overrides Condition 2 when "Ready to Publish" is detected. Is there a way to combine the two formulas so they wouldn't overr...

Formatting numbers
Pub2002/XP - how to format numbers in a table - I want it to automatically space for thousands etc and align the numbers correctly ie. 1 650 243 500 etc You will have to do it manually, or copy and paste it from a spreadsheet. -- See if Courier font will do it. Most other fonts do proportional spacing i.e. the letter m is wider than an l. In Courier (maybe teletype, typewriter and a few other fonts) use the same width of space and thus will line up columns from row to row. -- Don Vancouver, USA, a great city in one of the 45+ countries in America! "simonh" <si...

Looking up a date
Trying to get this to work, but no success: =LOOKUP(A6,{DATEVALUE("3/26/2010"),DATEVALUE("6/24/2010"),DATEVALUE("9/22/2010")},{"3","2","1"}) The value in A6 is a function; not hard-coded. It seems to work with one date and one result vector, but not two, and ultimately I will need several, maybe 7 or 8. Any ideas? Thanks! Ryan-- You can't use functions in array constants. >ultimately I will need several, maybe 7 or 8. Why don't you just create a table in ascending order by date: .............A.......

Custom Format for WEEKNUM(Today())
I am using WEEKNUM in Excel to do some COUNTIF functions and have problems with matches because Excel returns a single digit rather than 2 of weeks 1 throuhg 9 of any given year. Using teh COUNTIF function 20051 then mathmatically is the same as 200510. Is there some way to format the week returned to be 2 digits? -- RonB Hi Something like this: =TEXT(WEEKNUM(A1),"00") Arvi Laanemets "RonB" <ronb@discussions.microsoft.com> wrote in message news:AB373DCB-47DC-48C6-AB3C-9D9B284E4A73@microsoft.com... > I am using WEEKNUM in Excel to do some COUNTIF func...

Conditional Format Q
I wish to create a conditional format formula that will change the background colour in a cell to Blue, if the value of another cell is between to values (which are also detailed) Cell I want to change is D8 on Sheet1 The dependent cell of D8 is in A8 on Sheet1 The two values which A8 must be between/or equal to is in F7 & H7 on Sheet2 I just can't get my head around how to construct this Thanks Select cell D8. Format/ Conditional Formatting/ Formula Is/ =AND(A8>=Sheet2!F7,A8<=Sheet2!H7) if F7 is less than or equal to H7 or Format/ Conditional Formatting/ For...

can add multiple analysis code in a single FRx Row format
Currently we can add several analysis code in FRx Row format by using several rows (each row will contain only 1 analysis code - limitation of FRx with MDA). The client has many analysis codes in each analysis group. What they need is to add some of the analysis code in a single row; separating it into several rows will add more work in designing the FRx report. There should be a way for next releases of FRx to handle adding mulitple analysis codes in a single row or column of the report. ...

edit hyperlink code cont from 12/8/04
I tried running this without success. Can anybody tell me why? Sub Fix192Hyperlinks() ' Dim OldStr As String, NewStr As String OldStr = "c:\My Templates\Profile Database\" NewStr = "\\Pinoak\Data\Mfr\Grinding Room\" Dim hyp As Hyperlink For Each hyp In ActiveSheet.Hyperlinks hyp.Address = Replace(hyp.Address, OldStr, NewStr) Next hyp End Sub Thank you > > >> > "Ron de Bruin" wrote: > > > >> I copy this from David McRitchie his site > >> http://www.mvps.org/dmcritchie/excel/buildtoc...

Erratic Report Field Format
Hi: Having strange problem with erratic behavior of field format on a report/subreport, which I hope someone can solve. (Using Ac2003) On the subreport Detail, I have a field "Charges", Where Format= Fixed, Decimal Places = 2, and ControlSource = =Replace([ChargesApproved],"."," "). On the subreport Footer, I have a field "SumCharges", whose Format=Currency, Decimal places = 2, and ControlSource==DSum("[ChargesApproved]","TopN") (where TopN is the underlying query for the subreport). Then, in the Report Detail section, there is a fie...