2

I am working with a spreadsheet that includes a row where each cell contains the date of the first day in each month in a series of years: 1-Jan-18, 1-Feb-18, 1-Mar-18, ... , 1-Dec-18, 1-Jan-19, 1-Feb-19, etc.

I want these cells to only display the month they refer to, so I have them formatted to do this. The cells therefore read Jan, Feb, Mar etc, but when I click on any given cell the full date is displayed at the top of the sheet.

This all works fine, but I now want to get the =VALUE() of each of these dates. When I use the =VALUE() function, it complains that "Jan", "Feb", "Mar" etc "cannot be parsed to date/time": i.e., it doesn't recognise it as a date.

It seems that =VALUE() is taking the displayed date (e.g. Jan) as its input, rather than the stored date (e.g. 1-Jan-18). How can I get it to use the stored date? =VALUE("1-Jan-18") works fine, but =VALUE(A1) doesn't work if A1 = "1-Jan-18" but is set to display as "Jan".

EDIT: this seems only to be an issue in Google Sheets; Excel works as I would expect

  • How do you formatted the cell to display only the month? – Rubén Dec 4 '19 at 22:16
  • @Rubén Format > Number > More Formats > More date and time formats : in the dialog box that pops up I deleted the day and year and just left the month – Jim Dec 4 '19 at 22:18
1

Instead of =VALUE(A1) use =TO_PURE_NUMBER(A1)

Reference

| improve this answer | |
  • Fantastic, thank you! – Jim Dec 4 '19 at 22:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.