Month name to number of months YTD
I have a sheet with lots of year to date data by month, the last column on
the right, which is AN, contains the abbreviated name of the month for each row, formatted as m to show JAN, FEB,etc. This sheet is updated daily for year to date info. I am trying to figure out an average month to date, so am trying to figure out how to assign my JAN(in column AN) as the number of months YTD. So for instance in this months column AN, it displays JUL next to each row of information. in AO, I would like it to show 7 months current YTD, then next month show 8 and so on. That way I can use that cell to figure my average. How would I write a formula to do that? |
Month name to number of months YTD
Tasha wrote...
.... . . . So for instance in this months column AN, it displays JUL next to each row of information. in AO, I would like it to show 7 months current YTD, then next month show 8 and so on. . . . .... One way, =MATCH(AN2,{"Jan","Feb","Mar","Apr","May","Jun","J ul","Aug", "Sep","Oct","Nov","Dec"},0) |
Month name to number of months YTD
Harlan Grove wrote...
.... One way, =MATCH(AN2,{"Jan","Feb","Mar","Apr","May","Jun"," Jul","Aug", "Sep","Oct","Nov","Dec"},0) And a simpler way, =MONTH(AN2&"-1") |
Month name to number of months YTD
That worked perfect!!! Thank you thank you!!!
"Harlan Grove" wrote: Tasha wrote... .... . . . So for instance in this months column AN, it displays JUL next to each row of information. in AO, I would like it to show 7 months current YTD, then next month show 8 and so on. . . . .... One way, =MATCH(AN2,{"Jan","Feb","Mar","Apr","May","Jun","J ul","Aug", "Sep","Oct","Nov","Dec"},0) |
Month name to number of months YTD
Always love the shorter ways :)
Thanks!!! "Harlan Grove" wrote: Harlan Grove wrote... .... One way, =MATCH(AN2,{"Jan","Feb","Mar","Apr","May","Jun"," Jul","Aug", "Sep","Oct","Nov","Dec"},0) And a simpler way, =MONTH(AN2&"-1") |
Month name to number of months YTD
Harlan,
I know that you will have a reason for it so can I ask you why: =MONTH(AN2&"-1") When =MONTH(AN2&"0"), (or any other number from -99 to 99), seems to do the same thing? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Harlan Grove" wrote in message ups.com... Harlan Grove wrote... ... One way, =MATCH(AN2,{"Jan","Feb","Mar","Apr","May","Jun", "Jul","Aug", "Sep","Oct","Nov","Dec"},0) And a simpler way, =MONTH(AN2&"-1") |
Month name to number of months YTD
"Sandy Mann" wrote...
I know that you will have a reason for it so can I ask you why: =MONTH(AN2&"-1") When =MONTH(AN2&"0"), (or any other number from -99 to 99), seems to do the same thing? .... And they don't need to be quoted. Wouldn't have occurred to me that Excel would treat, e.g., "JUL0" as a valid date string. |
Month name to number of months YTD
Thank you Harlan.
The earliest reference that I can find in Google to it being used is Aladin Akyurek in February 2002 but I find that the new Google format is not a good as it used to be. -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Harlan Grove" wrote in message oups.com... "Sandy Mann" wrote... I know that you will have a reason for it so can I ask you why: =MONTH(AN2&"-1") When =MONTH(AN2&"0"), (or any other number from -99 to 99), seems to do the same thing? ... And they don't need to be quoted. Wouldn't have occurred to me that Excel would treat, e.g., "JUL0" as a valid date string. |
All times are GMT +1. The time now is 01:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com