# Return Month Name from Row and Day Number from Column Header



## hdunlap (Dec 15, 2022)

I am trying to populate AH5:AH17 in the "Sick Hours Used" with the month name (beginning of row) and the day of the month (column header) for any cell with an S, .75S, .5S or .2S no matter where they appear in B5:AF16.







ex: There is an .5s in cell W22 and I want to return Mar 22 in cell AH5 






I then need to use cell W22 to enter the number of hours in AJ5 with S=8, .75S=6, .5S=4, .2S=2






This is what I would like the end result to look like





Appreciate any assistance!


----------



## jdellasala (Dec 16, 2022)

Book1AFMNOPSAG151213141518Sick Hrs2Jan03Feb.75S0.754Mar05AprS.2S.75S1.956May.5S0.57Jun08Jul09Aug010Sep.2S0.211Oct.75S0.7512Nov013Dec0Sheet1Cell FormulasRangeFormulaA3:A13A3=EOMONTH(A2,1)AG2:AG13AG2=SUM(NUMBERVALUE(SUBSTITUTE(B2:AF2,"S","0")))+COUNTIFS(B2:AF2,"=S")
Don't understand how more than one date would fit into a single cell. Note that a LOT of columns are hidden. Please use *XL2BB* when posting data.


----------



## jdellasala (Dec 17, 2022)

Got it.
Book1ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH112345678910111213141516171819202122232425262728293031Sick Hrs2Jan 0.003Feb.75SFeb-150.754Mar 0.005AprS.2S.75SApr-5,13,181.956May.5SMay-120.507Jun 0.008Jul 0.009Aug 0.0010Sep.2SSep-120.2011Oct.75SOct-180.7512Nov 0.0013Dec 0.00Sheet2Cell FormulasRangeFormulaB1:AF1B1=SEQUENCE(,31)AG2:AG13AG2=IF(TEXTJOIN(",",TRUE,FILTER(B$1#,B2:AF2<>"",""))="","",TEXT(A2,"mmm")&"-"&TEXTJOIN(",",TRUE,FILTER(B$1#,B2:AF2<>"","")))AH2:AH13AH2=SUM(NUMBERVALUE(SUBSTITUTE(B2:AF2,"S","0")))+COUNTIFS(B2:AF2,"=S")A3:A13A3=EOMONTH(A2,1)Dynamic array formulas.


----------



## Peter_SSs (Dec 17, 2022)

Welcome to the MrExcel board!

The key in your row 18 says *.2*S = *1/2* sick yet your expected results indicate that you are treating *.2*S as *1/4* sick.
In either case are you really using .2S for 1/2 or 1/4 when you are using .75S for 3/4??? Surely you would use *.25*S for *1/4*?

Assuming that you use .25 for 1/4 and you have all the latest functions available, then try this.

22 12 17.xlsmABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI412345678910111213141516171819202122232425262728293031Sick Hours Used5JanHHMar 2246FebApr 587Mar.5sApr 1328AprS.25S.75SApr 1869May.5SHMay 12410JunSep 12211JulHOct 18612Aug13SepH.25S14Oct.75s15NovH16DecHSick HoursCell FormulasRangeFormulaAH5:AI11AH5=TEXTSPLIT(TEXTJOIN(",",1,IF(RIGHT(B5:AF16,1)="S",A5:A16&" "&B4:AF4&"|"&8*IF(B5:AF16="S",1,SUBSTITUTE(UPPER(B5:AF16),"S","")),"")),"|",",")Dynamic array formulas.


----------



## jdellasala (Dec 17, 2022)

Peter_SSs said:


> Welcome to the MrExcel board!
> 
> The key in your row 18 says *.2*S = *1/2* sick yet your expected results indicate that you are treating *.2*S as *1/4* sick.
> In either case are you really using .2S for 1/2 or 1/4 when you are using .75S for 3/4??? Surely you would use *.25*S for *1/4*?
> ...


Duho! I completely lost track of the value issue thinking that .# was the value of a day (almost!) in hours, but then failed to format it correctly anyway. Still, it's easier to do a straight XLOOKUP against a translation table which is easier to maintain should the S values change.
This is just the correction:
Book1AGAHAIAJAK1Sick HrsEntryValue2 0S83Feb-156.2S24 0.5S45Apr-5,13,1816.75S66May-1247 08 09 010Sep-12211Oct-18612  13  Sheet1Cell FormulasRangeFormulaAG2:AG13AG2=IF(TEXTJOIN(",",TRUE,FILTER(B$1#,B2:AF2<>"",""))="","",TEXT(A2,"mmm")&"-"&TEXTJOIN(",",TRUE,FILTER(B$1#,B2:AF2<>"","")))AH2:AH13AH2=SUM(XLOOKUP(FILTER(B2:AF2,B2:AF2<>"",0),SValues[Entry],SValues[Value],0))
I had to go with 0 as the *if not found* parameter, but it is UGLY! You'll notice in the two bottom rows (AH12:AH13) the value is 0 but the cells look blank. I simply formatted the cells with the custom format *#;;;*
The # displays a Positive number, what's BETWEEN the semicolons determines the format for Negative numbers, then Zeros, then Text. By providing no format for anything other than a Positive number, nothing is displayed.
I *think* the red triangles in the top left corner of Formula are formula error warning. I don't see them, but I have some error reporting turned off.
Thanks for keeping it 100!


----------



## Peter_SSs (Dec 17, 2022)

jdellasala said:


> I *think* the red triangles in the top left corner of Formula are formula error warning. I don't see them,


No, the triangles in the XL2BB mini sheets simply indicate that those cells contain formulas.

(BTW, the 4th image in post #1, prefixed by "This is what I would like the end result to look like" does not have the months in column AH spread out and aligned with the months in column A as you have done.)


----------



## hdunlap (Dec 19, 2022)

Peter_SSs said:


> Welcome to the MrExcel board!
> 
> The key in your row 18 says *.2*S = *1/2* sick yet your expected results indicate that you are treating *.2*S as *1/4* sick.
> In either case are you really using .2S for 1/2 or 1/4 when you are using .75S for 3/4??? Surely you would use *.25*S for *1/4*?
> ...


Thank you. This is really close. 
I need the hours separate though, so I can total them. 
I corrected the key. You are correct and it should be .25S=1/4 Sick. 
Lastly, I made all of the months three letters and would like to exclude the "**" in front of Nov & Dec.


----------



## Peter_SSs (Dec 19, 2022)

Try this then.

hdunlap.xlsmABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI412345678910111213141516171819202122232425262728293031Sick Hours Used5JanHHMar 2246FebApr 587Mar.5sApr 1328AprS.25S.75SApr 1869May.5SHMay 12410JunSep 12211JulHOct 18612AugDec 12413SepH.25S14Oct.75s15**NovH16**Dec.5SH1718Total36Sick Hours (2)Cell FormulasRangeFormulaAH5:AH12AH5=TOCOL(IF(RIGHT(B5:AF16,1)="S",SUBSTITUTE(A5:A16,"*","")&" "&B4:AF4,x),2)AI5:AI12AI5=LET(h,TOCOL(IF(RIGHT(B5:AF16,1)="S",B5:AF16,x),2),8*IF(h="S",1,LEFT(h,LEN(h)-1)))AI18AI18=SUM(AI5#)Dynamic array formulas.


----------



## hdunlap (Dec 20, 2022)

Peter_SSs said:


> Try this then.
> 
> hdunlap.xlsmABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI412345678910111213141516171819202122232425262728293031Sick Hours Used5JanHHMar 2246FebApr 587Mar.5sApr 1328AprS.25S.75SApr 1869May.5SHMay 12410JunSep 12211JulHOct 18612AugDec 12413SepH.25S14Oct.75s15**NovH16**Dec.5SH1718Total36Sick Hours (2)Cell FormulasRangeFormulaAH5:AH12AH5=TOCOL(IF(RIGHT(B5:AF16,1)="S",SUBSTITUTE(A5:A16,"*","")&" "&B4:AF4,x),2)AI5:AI12AI5=LET(h,TOCOL(IF(RIGHT(B5:AF16,1)="S",B5:AF16,x),2),8*IF(h="S",1,LEFT(h,LEN(h)-1)))AI18AI18=SUM(AI5#)Dynamic array formulas.


This works perfectly! Thank you.


----------



## Peter_SSs (Dec 20, 2022)

You're welcome. Thanks for the follow-up.


----------

