Return Month Name from Row and Day Number from Column Header

H

hdunlap

New Member
Joined
Dec 15, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.


1671136201157.jpeg


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

1671136393086.png



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


1671136457365.png


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

1671136148001.png


Appreciate any assistance!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
Book1
AFMNOPSAG
151213141518Sick Hrs
2Jan0
3Feb.75S0.75
4Mar0
5AprS.2S.75S1.95
6May.5S0.5
7Jun0
8Jul0
9Aug0
10Sep.2S0.2
11Oct.75S0.75
12Nov0
13Dec0
Sheet1
Cell Formulas
RangeFormula
A3: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.
 
Upvote 0
Got it.
Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
112345678910111213141516171819202122232425262728293031Sick Hrs
2Jan 0.00
3Feb.75SFeb-150.75
4Mar 0.00
5AprS.2S.75SApr-5,13,181.95
6May.5SMay-120.50
7Jun 0.00
8Jul 0.00
9Aug 0.00
10Sep.2SSep-120.20
11Oct.75SOct-180.75
12Nov 0.00
13Dec 0.00
Sheet2
Cell Formulas
RangeFormula
B1: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.
 
Upvote 0
Welcome to the MrExcel board!

The key in your row 18 says .2S = 1/2 sick yet your expected results indicate that you are treating .2S 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 .25S 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.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI
412345678910111213141516171819202122232425262728293031Sick Hours Used
5JanHHMar 224
6FebApr 58
7Mar.5sApr 132
8AprS.25S.75SApr 186
9May.5SHMay 124
10JunSep 122
11JulHOct 186
12Aug
13SepH.25S
14Oct.75s
15NovH
16DecH
Sick Hours
Cell Formulas
RangeFormula
AH5: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.
 
Last edited:
Upvote 0
Peter_SSs said:
Welcome to the MrExcel board!

The key in your row 18 says .2S = 1/2 sick yet your expected results indicate that you are treating .2S 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 .25S 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.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI
412345678910111213141516171819202122232425262728293031Sick Hours Used
5JanHHMar 224
6FebApr 58
7Mar.5sApr 132
8AprS.25S.75SApr 186
9May.5SHMay 124
10JunSep 122
11JulHOct 186
12Aug
13SepH.25S
14Oct.75s
15NovH
16DecH
Sick Hours
Cell Formulas
RangeFormula
AH5: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.
Click to expand...
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:
Book1
AGAHAIAJAK
1Sick HrsEntryValue
2 0S8
3Feb-156.2S2
4 0.5S4
5Apr-5,13,1816.75S6
6May-124
7 0
8 0
9 0
10Sep-122
11Oct-186
12  
13  
Sheet1
Cell Formulas
RangeFormula
AG2: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!
 
Upvote 0
jdellasala said:
I think the red triangles in the top left corner of Formula are formula error warning. I don't see them,
Click to expand...
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.)
 
Upvote 0
Peter_SSs said:
Welcome to the MrExcel board!

The key in your row 18 says .2S = 1/2 sick yet your expected results indicate that you are treating .2S 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 .25S 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.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI
412345678910111213141516171819202122232425262728293031Sick Hours Used
5JanHHMar 224
6FebApr 58
7Mar.5sApr 132
8AprS.25S.75SApr 186
9May.5SHMay 124
10JunSep 122
11JulHOct 186
12Aug
13SepH.25S
14Oct.75s
15NovH
16DecH
Sick Hours
Cell Formulas
RangeFormula
AH5: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.
Click to expand...
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.

1671470308872.png
 
Upvote 0
Try this then.

hdunlap.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI
412345678910111213141516171819202122232425262728293031Sick Hours Used
5JanHHMar 224
6FebApr 58
7Mar.5sApr 132
8AprS.25S.75SApr 186
9May.5SHMay 124
10JunSep 122
11JulHOct 186
12AugDec 124
13SepH.25S
14Oct.75s
15**NovH
16**Dec.5SH
17
18Total36
Sick Hours (2)
Cell Formulas
RangeFormula
AH5: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.
 
Upvote 0
Solution
Peter_SSs said:
Try this then.

hdunlap.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI
412345678910111213141516171819202122232425262728293031Sick Hours Used
5JanHHMar 224
6FebApr 58
7Mar.5sApr 132
8AprS.25S.75SApr 186
9May.5SHMay 124
10JunSep 122
11JulHOct 186
12AugDec 124
13SepH.25S
14Oct.75s
15**NovH
16**Dec.5SH
17
18Total36
Sick Hours (2)
Cell Formulas
RangeFormula
AH5: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.
Click to expand...
This works perfectly! Thank you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Adding cells with numbers and a letter designation to specific cells
Replies
4
Views
494
kweaver
kweaver
J
  • Solved
Sum row range based on column header criteria
Replies
6
Views
478
ExceLoki
ExceLoki
D
  • Question
Applying dupicate Column Headers in an Excel Table
Replies
6
Views
394
awoohaw
awoohaw
N
  • Solved
VBA code for deleting rows based on number of days in a month
2
Replies
14
Views
1K
nitiona
N
P
  • Question
How to use a MATCH helper column containing rows from a different sheet and INDEX in an array formula to return based on data in the helper column?
Replies
0
Views
171
purekarting
P

Forum statistics

Threads
1,215,012
Messages
6,122,682
Members
449,091
Latest member
peppernaut

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top