Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 66
Default First Day of the month

Does anyone know how to have excel look at a cell that has a date that is
formatted as xx/xx/xxxx (E.g. Cell A2 = 01/16/2007) and show the date as
01/01/2007 in B2, another words view as date as first day of the month.
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: First Day of the month

Yes, you can use the following formula in cell B2 to display the first day of the month based on the date in cell A2:
  1. `=DATE(YEAR(A2),MONTH(A2),1)`

To apply this formula to other cells in column B, you can simply copy and paste the formula down the column. Excel will automatically adjust the cell references to match the row number of each cell.

Let me know if you need any further assistance.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default First Day of the month

In B2:

=DATE(YEAR(A2),MONTH(A2),1)

--
Gary''s Student - gsnu201001


"Jen_T" wrote:

Does anyone know how to have excel look at a cell that has a date that is
formatted as xx/xx/xxxx (E.g. Cell A2 = 01/16/2007) and show the date as
01/01/2007 in B2, another words view as date as first day of the month.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default First Day of the month

On Thu, 25 Mar 2010 09:21:02 -0700, Jen_T
wrote:

Does anyone know how to have excel look at a cell that has a date that is
formatted as xx/xx/xxxx (E.g. Cell A2 = 01/16/2007) and show the date as
01/01/2007 in B2, another words view as date as first day of the month.


B2: =A2-DAY(A2)+1
--ron
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default First Day of the month

Hi,

You may also try this

=EOMONTH(F7,-1)+1

You may need to install the analysis Toolpak for the eomonth function to
work

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Jen_T" wrote in message
...
Does anyone know how to have excel look at a cell that has a date that is
formatted as xx/xx/xxxx (E.g. Cell A2 = 01/16/2007) and show the date as
01/01/2007 in B2, another words view as date as first day of the month.


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
excel to make the days cary over month to month automaticly GARY New Users to Excel 1 April 19th 08 06:05 PM
Excel 2003 month to month data change grid Chad[_2_] Excel Discussion (Misc queries) 2 February 15th 08 02:36 AM
Retrieve data for previous 3, 6, 12 month given current month GB Excel Worksheet Functions 4 July 19th 07 11:58 PM
transfer cell $ amount to other sheet month-to-month without overc Colin2u Excel Discussion (Misc queries) 1 July 28th 05 02:36 AM


All times are GMT +1. The time now is 06:48 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"