Excel - Split date/time/timezone into date only

Question

I am working with an extract which only contains the following date format (I cannot change it to any other unfortunately): 2020-09-01 12:02:54 +0300

I need to take only the year and the month of that format - Example: only 9 (or September would be even better) and 2020

Is there way that I can split that in Excel? I need it as a formula, because I add daily extracts and need to split this every day.

Answer 1

Thanks for posting your question.  I am an Excel user like you.

The issue is that Excel doesn't recognize this as a date so we will have to convert it to a date value first before we can format it.  Assuming that the data is always in the same format (yyyy-mm-dd) and you are extracting this to another column, put this formula in the other column:

=DATEVALUE(LEFT(A1,10))

(A1 would be replaced with the cell your data is in) and then format the date any way you want it to show.  The month, September, in your example would be a custom format:  mmmm as in:

You can drag the formula down and as new data is entered or replaced, the formula will extract the date from it.  Make sure that all of the cells where you drag the formula are formatted according to the date format you choose.

Reply again if you have any additional questions.  Please mark as answered if this solves your question.

Rich~M

Answer 2

The issue here is that the entries in column F are date values now even though we have formatted it to look like a month.  In order to match it to the text in B2, we will need to make those date values into text as well.  Use this formula in F3 and drag down instead of the one I gave above:

=TEXT(DATEVALUE(LEFT(D3,10)),"MMMM")

Your COUNTIF formula should work now.  By the way, the column/cell date formatting will no longer apply so if you want a different date format than "mmmm" you would need to put it in this formula rather than in the "Format Cells" dialogue box.

Reply again if you have any additional questions and please mark this as answered if this solves your question.

Rich~M

Answer 3

If you want to have the month and year type in mmmm-yyyy in the Type: box as below:

Rich~M

Answer 4

Thank you for the suggestion. Now I am met with another issue. I need to use the COUNTIFS and one of the criteria is to match a particular cell.

I have attached an example:

B4 should have 3 as result, but it comes with 0.

Any idea how to go around this obstacle?

Answer 5

Thank you for your help, it did answer and resolve my issue!