Simple Date Formula

[dragonmouse]

I exported an excel spreadsheet using Crystal. Crystal insists on storing the date regardless of format as "3/14/2019 4:04:26 PM" when it's exported. I have some formulas in which I want to look for "Mar-2019". Formatting in Excel doesn't help. It still sees the date as "3/14/2019 4:04:26 PM". What is a formula that will convert this "3/14/2019 4:04:26 PM" to "Mar-2019"

 

[jtakw]

Hi,

Try these:

Book3.xlsx
	B	C	D	E	F	G
3	3/14/2019 4:04:26 PM	Mar-2019	Mar-2019		FALSE	TRUE
4			^Custom format mmm-yyyy
Sheet720

Cell Formulas
Range	Formula
C3	C3	=TEXT(INT(B3),"mmm-yyyy")
D3	D3	=INT(B3)
F3:G3	F3	=ISNUMBER(C3)

 

[dragonmouse]

Hi,

Try these:

Book3.xlsx
	B	C	D	E	F	G
3	3/14/2019 4:04:26 PM	Mar-2019	Mar-2019		FALSE	TRUE
4			^Custom format mmm-yyyy
Sheet720

Cell Formulas
Range	Formula
C3	C3	=TEXT(INT(B3),"mmm-yyyy")
D3	D3	=INT(B3)
F3:G3	F3	=ISNUMBER(C3)

Thank you...sadly it doesn't quite convert the number to the format. It is a cool formula though.

I'm trying to get this formula to work:

=SUMPRODUCT(COUNTIF($N:$N,"Mar-19"))

once this date "1/13/2021 9:38:13 AM" is translated to "MAR-19" I want to be able to count all the Mar-19 items in the spread sheet. I can subtotal them but I really want to summarize items.

 

[AhoyNC]

Maybe:

Book1
	A	B
1	3/14/2019 4:04:26 PM	Mar-19
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TEXT(INT(A1*1),"mmm-yy")

 

[dragonmouse]

Hi,

Try these:

Book3.xlsx
	B	C	D	E	F	G
3	3/14/2019 4:04:26 PM	Mar-2019	Mar-2019		FALSE	TRUE
4			^Custom format mmm-yyyy
Sheet720

Cell Formulas
Range	Formula
C3	C3	=TEXT(INT(B3),"mmm-yyyy")
D3	D3	=INT(B3)
F3:G3	F3	=ISNUMBER(C3)

We HAVE a WINNER ?? THANK YOU. that works. I guess it wanted "TEXT" for my counting formula. It's going to be GLORIOUS!!! THANK YOU SOOOOOOOO MUCH. Not sure why the first one didn't work...maybe I just typed something wrong when I translated it to my spreadsheet.