Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default Generating date from day, month year

Hello all,

Is there any way I can generate a date in one cell, from 3 other separate
cells - one each for day, month, year? i.e. if you enter (for example) '01'
in A1, 'September' in A2 and '2009' in A3, then in B1 it shows '01/09/2009'.
Alternatively, is there a way I can generate the month from the date - so if
you enter (for example) '01/09/2009' in A1, it shows 'September' in B1?
Many thanks in advance for your help,
N
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default Generating date from day, month year

"Ned Harrison" wrote:
Is there any way I can generate a date in one cell, from 3 other separate
cells - one each for day, month, year? i.e. if you enter (for example)
'01'
in A1, 'September' in A2 and '2009' in A3, then in B1 it shows
'01/09/2009'.


I assume the apostrophes (single quotes) are not really in the cell. And I
assume you want the date value (number) 1/9/2009, not text. Try:

=--(A1&A2&A3)

formatted as Date or the custom format mm/dd/yyyy, depending on Regional and
Language settings.

On the off-chance that you want the text "01/09/2009", try:

=text(A1&A2&A3, "mm/dd/yyyy")


Alternatively, is there a way I can generate the month from the date - so
if
you enter (for example) '01/09/2009' in A1, it shows 'September' in B1?


=text(A1, "mmmm")


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 174
Default Generating date from day, month year

Hi Ned,

For the date, try

=DATEVALUE(A1&"/"&A2&"/"&A3) with A1 = day, A2 = September, A3 = year

To get the month of a given date, try to format the cell: number, custom:
'mmmm' (without the quotes).

Wkr,

JP

"Ned Harrison" wrote in message
...
Hello all,

Is there any way I can generate a date in one cell, from 3 other separate
cells - one each for day, month, year? i.e. if you enter (for example)
'01'
in A1, 'September' in A2 and '2009' in A3, then in B1 it shows
'01/09/2009'.
Alternatively, is there a way I can generate the month from the date - so
if
you enter (for example) '01/09/2009' in A1, it shows 'September' in B1?
Many thanks in advance for your help,
N



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default Generating date from day, month year

Col A Col B
1 =DATEVALUE(A1&"-"&A2&"-"&A3)
September =TEXT(B1,"mmmm")
2009

If this post helps click Yes
---------------
Jacob Skaria


"Ned Harrison" wrote:

Hello all,

Is there any way I can generate a date in one cell, from 3 other separate
cells - one each for day, month, year? i.e. if you enter (for example) '01'
in A1, 'September' in A2 and '2009' in A3, then in B1 it shows '01/09/2009'.
Alternatively, is there a way I can generate the month from the date - so if
you enter (for example) '01/09/2009' in A1, it shows 'September' in B1?
Many thanks in advance for your help,
N

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default Generating date from day, month year

Thanks all - that's brilliant.
Cheers,
Ned
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
count month when date is in day/month/year format ccKennedy Excel Worksheet Functions 6 April 30th 09 03:32 AM
=DATE(YEAR(P4),MONTH(P4)+1,1) [email protected] Excel Worksheet Functions 3 January 22nd 09 05:37 PM
Tell me which "season" (Month/Day through Month/Day) a date(Month/Day/Year) falls in (any year)??? misscrf Excel Discussion (Misc queries) 1 December 14th 07 03:59 PM
Date -Month/day/year mfg Excel Discussion (Misc queries) 4 August 9th 07 11:26 PM
Sort month/date/year data using month and date only SMW820 Excel Discussion (Misc queries) 6 June 22nd 06 05:14 PM


All times are GMT +1. The time now is 03:03 AM.

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"