Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
PCLIVE
 
Posts: n/a
Default Changing Cell formats to date fields automatically

I have a cell in which its value is determined by 3 other cells in order to
form a Date type of entire.

Example:
Cells O17, O18, and O19 are determined by combo box selections.

O17 = Month
O18 = Day
O19 = Year

Let's say that cell O20 uses the following formula:
=O17 & " " & O18 & "," & " " & O19

Cell O20 would be displayed as:
April 1, 2005

Is there a way I can force cell O20 to be seen as a date? I've adjusted the
cell formatting to 'Date' and I used the type as 'March 14, 1998'. However,
cell O20 is not in a Date format.

If I click in the formula bar for cell O20 and press enter, it then switches
to the Date format.
I need for this to be done automatically without me having to click in each
cell. I'm already using a macro to copy this cell from a different
worksheet. Can I add something to the macro to force this into a date
fomat?

Thanks,
Paul


  #2   Report Post  
JE McGimpsey
 
Posts: n/a
Default

One way:

=DATEVALUE(O18 & O17 & O19)


In article ,
"PCLIVE" wrote:

I have a cell in which its value is determined by 3 other cells in order to
form a Date type of entire.

Example:
Cells O17, O18, and O19 are determined by combo box selections.

O17 = Month
O18 = Day
O19 = Year

Let's say that cell O20 uses the following formula:
=O17 & " " & O18 & "," & " " & O19

Cell O20 would be displayed as:
April 1, 2005

Is there a way I can force cell O20 to be seen as a date? I've adjusted the
cell formatting to 'Date' and I used the type as 'March 14, 1998'. However,
cell O20 is not in a Date format.

If I click in the formula bar for cell O20 and press enter, it then switches
to the Date format.
I need for this to be done automatically without me having to click in each
cell. I'm already using a macro to copy this cell from a different
worksheet. Can I add something to the macro to force this into a date
fomat?

Thanks,
Paul

  #3   Report Post  
PCLIVE
 
Posts: n/a
Default

You are awesome!

Thank you.
Paul


"JE McGimpsey" wrote in message
...
One way:

=DATEVALUE(O18 & O17 & O19)


In article ,
"PCLIVE" wrote:

I have a cell in which its value is determined by 3 other cells in order
to
form a Date type of entire.

Example:
Cells O17, O18, and O19 are determined by combo box selections.

O17 = Month
O18 = Day
O19 = Year

Let's say that cell O20 uses the following formula:
=O17 & " " & O18 & "," & " " & O19

Cell O20 would be displayed as:
April 1, 2005

Is there a way I can force cell O20 to be seen as a date? I've adjusted
the
cell formatting to 'Date' and I used the type as 'March 14, 1998'.
However,
cell O20 is not in a Date format.

If I click in the formula bar for cell O20 and press enter, it then
switches
to the Date format.
I need for this to be done automatically without me having to click in
each
cell. I'm already using a macro to copy this cell from a different
worksheet. Can I add something to the macro to force this into a date
fomat?

Thanks,
Paul



  #4   Report Post  
Duke Carey
 
Posts: n/a
Default

in O20 use

=DATEVALUE(O17 & " " & O18 & "," & " " & O19)


"PCLIVE" wrote:

I have a cell in which its value is determined by 3 other cells in order to
form a Date type of entire.

Example:
Cells O17, O18, and O19 are determined by combo box selections.

O17 = Month
O18 = Day
O19 = Year

Let's say that cell O20 uses the following formula:
=O17 & " " & O18 & "," & " " & O19

Cell O20 would be displayed as:
April 1, 2005

Is there a way I can force cell O20 to be seen as a date? I've adjusted the
cell formatting to 'Date' and I used the type as 'March 14, 1998'. However,
cell O20 is not in a Date format.

If I click in the formula bar for cell O20 and press enter, it then switches
to the Date format.
I need for this to be done automatically without me having to click in each
cell. I'm already using a macro to copy this cell from a different
worksheet. Can I add something to the macro to force this into a date
fomat?

Thanks,
Paul



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Change cell color dependin on date ... T. Denford Excel Worksheet Functions 4 April 13th 05 01:59 AM
? on "date last saved" in cell chris w Excel Worksheet Functions 2 January 20th 05 01:51 AM
Excel: How do I enter a date in one cell & automatically dates adj Shannon Excel Discussion (Misc queries) 4 January 12th 05 01:51 AM
prevent 4/5 in a cell automatically transfer to a date format kei Excel Discussion (Misc queries) 3 December 9th 04 12:52 PM
Combined date time cell to separate date & time components Mark Ada Excel Worksheet Functions 1 December 2nd 04 01:04 AM


All times are GMT +1. The time now is 02:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"