Calling all Excel Guru...Again!

Discussion in 'Mac Apps and Mac App Store' started by whohasaquestion, Mar 19, 2015.

  1. whohasaquestion macrumors 6502

    Joined:
    Sep 17, 2011
    #1
    Alright, this time I made a file so you guys can see what I want to achieve:

    https://onedrive.live.com/redir?resi...154C70!12160

    So there are two tabs in this workbook. On the "Good" tab, the dates are converted to the first of the month. So for instance, 3/13/14 on the "Date" column will be 3/01/14 on the "MM/YY" column.

    On the "Bad" tab however, the "MM/YY" looks the same as in the "Good" tab but still retains the same date value as in "Date" column. SO for instance, 3/13/14 on "date" is still 3/13/14 on "MM/YY".

    The MM/YY on the "good" tab is the result I want. I did it once...remember it involved pasting to a clipboard but forgot how to do it again. Can anyone enlighten me? thank you.
     
  2. saberahul macrumors 68040

    Joined:
    Nov 6, 2008
    Location:
    USA
    #2
    Your file says it is deleted. Regardless, if you're trying to get the first of the month for a given date, you could try:

    Say A1 is 3/13/2015. In B1, you would put:

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

    If you would like to get the end of month, there's a built-in formula which would be = eomonth(A1, 0)
     
  3. whohasaquestion thread starter macrumors 6502

    Joined:
    Sep 17, 2011
    #3
    wasn't aware of the eomonth formula. Awesome!

    I just thought there is a way to copy and paste to lose the date value and only retain the date format.
     
  4. dyt1983, Mar 19, 2015
    Last edited: Jun 2, 2015

    dyt1983 macrumors 65816

    Joined:
    May 6, 2014
    Location:
    USA USA USA
    #4
    edit: To remove personally identifying information not relevant to the thread.
     
  5. saberahul macrumors 68040

    Joined:
    Nov 6, 2008
    Location:
    USA
    #5
    Not sure what you mean. If you want to maintain the "date" format, you can use Format Painter from the original to the new cell(s). You can also pick the Date format from the dropdown in your toolbar above.
     
  6. whohasaquestion thread starter macrumors 6502

    Joined:
    Sep 17, 2011
    #6
    Nevermind. The EOMONTH function works wonder. My problem solved. Thanks!
     
  7. jaduff46 macrumors regular

    jaduff46

    Joined:
    Mar 3, 2010
    Location:
    Northern NJ
    #7
    FYI and future reference, you can also just Paste Special\Format
     

Share This Page