logics for particular dates for overtime sheets in excel

I am working on overtime sheet for a institution. I made a sheet where I used date from 1 to 31 of days. However, over time is appearing in random days. Therefore, I need to write this days manually. Thus, I want to apply logics if input OT into the overtime date (OT = overtime) then that particular date will be shown in the other rows. Is there any formula available?

I think I understand what you're trying to achieve:

Assuming you have row of dates in cells B9:AF9

1. In cells B10:AF10 input "OT" where needed

Or, if you enter "OT" according to people's names, add a formula there to find out if you anyone had OT on this date

=IF(COUNTIF(B11:B24,"OT")>0,"OT","")

or if your Excel uses semicolon ";" not comma "," :

=IF(COUNTIF(B11:B24;"OT")>0;"OT";"")

it will indicate if we had OT on this day.

2. In cell D30 paste a formula

=IFERROR(INDEX($B$9:$AF$9,1,SMALL(IF(($B$10:$AF$10="ot")*($B$9:$AF$9>0)>0,MATCH(COLUMN($A$9:$AE$9),COLUMN($A$9:$AE$9),0),""),COLUMN(A1))),"")

but insert it as an array formula, pressing Ctrl+Shift+Enter simultaniously.

If your Excel uses semicolon as parameter separator use this one:

=IFERROR(INDEX($B$9:$AF$9;1;SMALL(IF(($B$10:$AF$10="ot")*($B$9:$AF$9>0)>0;MATCH(COLUMN($A$9:$AE$9);COLUMN($A$9:$AE$9);0);"");COLUMN(A1)));"")

insert it as an array formula, pressing Ctrl+Shift+Enter simultaniously.

3. Copy and paste it to cells E30 - AH30.

It will give us an array of dates where you put 'OT' in the upper table.