excel formula to get start of year

Posted on 2011-10-06
Last Modified: 2012-05-12
I have a cell named currentMonth. It contains a date, ie, "7/1/2011".

I want to place a formula in another cell, such that this cell will contain the value Jan 1 for the year of currentMonth. So in this example, the second cell will contain the value "1/1/2011". This is kind of what I mean, although it doesn't work:

="1/1/" + YEAR(currentMonth)

This second cell will also need to be formatted as a date.
Question by:bassman592
    LVL 8

    Expert Comment

    currentMonth needs to be a valid date, and not just text.
    LVL 1

    Expert Comment

    Please try =Date(year(today()),1,1)
    LVL 1

    Accepted Solution

    Please replace today() with CurrentMonth (which should be a valid date).

    Author Closing Comment

    Perfect! Thanks.

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    Suggested Solutions

    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now