0

enter image description hereenter image description hereenter image description hereenter image description hereenter image description hereenter image description hereenter image description hereenter image description hereenter image description hereenter image description hereI 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?

enter image description here

Turjoy Ahmed
  • 29
  • 9
  • can you show an example of how the data looks like please? – Scinana May 23 '21 at 17:48
  • I have added an Image. dated is like that. If it will have been over time then I will input only ot @Scinana – Turjoy Ahmed May 23 '21 at 17:58
  • what do you mean by overtime? – Scinana May 23 '21 at 18:15
  • over time in office. like general work time is 8 hours but if anyone work for more than 8 hours then it will be called overtime. For example if any one work for 10 ours then 2 ours will be over time @Scinana – Turjoy Ahmed May 23 '21 at 18:31
  • But let's say we have overtime at `05-10-21` where exactly will you type `OT` and what will it trigger? (What are you trying to accomplish?). Could you show an example of what you expect? – P.b May 23 '21 at 19:02
  • If I insert "OT" then the particular date will be showing in the other table. I am adding a picture again @Scinana – Turjoy Ahmed May 23 '21 at 19:12
  • " input OT into the overtime date (OT = overtime) " >> which cell ? | "then that particular date will be shown in the other rows." >> which row ? – p._phidot_ May 24 '21 at 08:34
  • there are not any specific rows. Because of this OT is going to a random date. Therefore İ have to put the input each of the data rows. Then İ will input OT then the date will show in the new table @Scinana – Turjoy Ahmed May 24 '21 at 13:07

1 Answers1

1

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.

  1. 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.

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

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

Overtime formula picture

Andrew Doroshenko
  • 261
  • 1
  • 6
  • Hello, two questions: 1) In what language are the formulas in your Excel, English or Turkish? For example, do you write SUM(A:A) or TOPLA(A:A) ? 2) What do you use to separate parameters, comma or semicolon? For example, do you write SUMIF(A:A, B1) or SUM(A:A; B1) ? – Andrew Doroshenko May 25 '21 at 05:54
  • Glad to hear that it's working, kindly mark the answer as accepted please :). – Andrew Doroshenko May 25 '21 at 06:20
  • @TurjoyAhmed I edited the answer - removed sheet references and added a version with semicolons, could you try now? – Andrew Doroshenko May 25 '21 at 06:43
  • İ don't find a result yet. İ am going to attach the full-page images (two images). İn more clear İ am using rows A10 to AF 10 and column A10 to A 23. I want to put the data input over the date rows so that İ want to find the resuşt on the other table which is D 29 to Q 43 rows @AndrewDoroshenko – Turjoy Ahmed May 25 '21 at 07:15
  • @TurjoyAhmed Okay, so if you write "OT" in the upper table (range **B10:AF23**), you will see corresponding **dates** in the lower table header (range **B29:AF29**). Is it what we are trying to achieve? – Andrew Doroshenko May 25 '21 at 07:46
  • @TurjoyAhmed in the yellow cells you will input just letters "OT", correct? Or you want to enter some numbers there? Just to be sure – Andrew Doroshenko May 25 '21 at 08:14
  • Yes İ will insert here OT then the corresponding dates will show over the blue row serially @AndrewDoroshenko – Turjoy Ahmed May 25 '21 at 08:15
  • @TurjoyAhmed I see, I will edit the answer now. You will not need the first small formula then, just the second one. I thought you were going to enter 'OT' according to people's names. – Andrew Doroshenko May 25 '21 at 08:21
  • İ did it as your recommendation. However, I am unable to find the result. İt should be B10:AF10. And the output will so in D:30 as it reqıred cell. For example, it will be 10 days of overtime then İ will input OT at the following date and that date will be showing in D30 to after it 9 cells i.e. D30:L30 @AndrewDoroshenko – Turjoy Ahmed May 25 '21 at 09:34
  • @TurjoyAhmed I didn't notice that the row of dates has changed in your file. It was 29 in the previous picture and now it's 30. So paste it in D30 please. Could you clarify, why do you have some error in D31:D42? Now I will add a picture of what I got and please tell me if I understand your goal. – Andrew Doroshenko May 25 '21 at 10:31
  • Yes exactly, You are getting the correct result and I am also looking for the same result. But İ am getting an error – Turjoy Ahmed May 25 '21 at 10:46
  • It's an array formula, so what needs to be done is: 1) click on D30, 2) press Delete if there is something already there, 3) press F2 or double-click to start editing, 4) press Ctrl+V to paste the formula, 5) press Shift+Ctrl+Enter to finish editing. After these steps if the formula works for the cell in column D, we need to populate it to range E30-AH30. In order to do that we need to 1) press Ctrl+C to copy the cell; 2) highlight the range E30-AH30; 3) press Ctrl+V to paste. – Andrew Doroshenko May 25 '21 at 11:28
  • Here realize that maybe İ miss out on something. Have İ needed to write any formula in B10:AF10? Because İ insert input as OT but I am still unable to find the result. İ am going to attach the images for more clearance @AndrewDoroshenko – Turjoy Ahmed May 25 '21 at 14:43
  • @TurjoyAhmed, No, you don't have to. If you enter OT manually in B10:AF10 it's okay. Just follow explanation in the answer and in my previous comment step by step. Delete all the errors in D30:D42 before pasting. Why do you have those errors in D31:D42? And what do you mean by "unable to find the result"? What exactly do you see? – Andrew Doroshenko May 25 '21 at 15:09
  • İ inserted OT in B10:AF10 but the corresponding date does not appear in the D29:AF29. Moreover, write everything as your recommendation. But it does not show the output yet. @AndrewDoroshenko – Turjoy Ahmed May 26 '21 at 06:22
  • Please post a picture so that I could see what is inside D29. Just click D29 before making a screenshot -@TurjoyAhmed – Andrew Doroshenko May 26 '21 at 06:40
  • İ have updated the post with images for D29 @AndrewDoroshenko – Turjoy Ahmed May 26 '21 at 10:22
  • @TurjoyAhmed the formula looks good... You get this error("#AD?") when there is a typo in a function name. Maybe your Excel doesn't support one of the functions in the formula? Can you check them one by one just by starting typing anywhere "=iferror", then "=small" etc, and see if there is a pop-up wizard for all of them (IFERROR, INDEX, SMALL, IF, MATCH, COLUMN). Just to make sure all these functions exist in your Excel. – Andrew Doroshenko May 26 '21 at 11:24
  • İ check it several times. But İ didn't find anything else. İf you want İ can share the file over email @AndrewDoroshenko – Turjoy Ahmed May 26 '21 at 12:56
  • @TurjoyAhmed okay, maybe you can upload it to any file share platform and post a link here? – Andrew Doroshenko May 26 '21 at 13:26
  • Could you please precise it? Where may İ upload this file? @AndrewDoroshenko – Turjoy Ahmed May 27 '21 at 05:06
  • You can upload it to any file sharing service, for example, Google Drive, and then right click on it and click 'get link' and then paste this link here @TurjoyAhmed – Andrew Doroshenko May 27 '21 at 05:30
  • Hereby I am sharing the link. Please find the attachment. @AndrewDoroshenko https://www.dropbox.com/scl/fi/x7ne8y1zxovmlsis7lxg3/Mesai-up_25_05_2021.xlsx?dl=0&rlkey=a629w0mqwkw7lpo7ukmsy4tg7 – Turjoy Ahmed May 27 '21 at 05:41
  • @TurjoyAhmed it works for me. When I open the file, I see an error (#NAME?) which is your (#AD?). I just clicked on D29, pressed F2, then Ctrl+Shift+Enter and it worked... Will try and look into it further but for now I don't see why is it happening. This error indicates that a function name is wrong or misspelled. – Andrew Doroshenko May 27 '21 at 06:23
  • Could you please check the formula again that was correctly working on Your pc yesterday @AndrewDoroshenko – Turjoy Ahmed May 27 '21 at 06:31
  • Not yet. Could You please edit it on Your pc and share the file? @AndrewDoroshenko – Turjoy Ahmed May 27 '21 at 14:04
  • @TurjoyAhmed https://drive.google.com/file/d/1RraRYi7MzEmDpLt6pEKNYO7po2vJXyWW/view?usp=sharing – Andrew Doroshenko May 27 '21 at 14:07
  • So kind of You. İt is now working. Thanks a lot – Turjoy Ahmed May 27 '21 at 14:11
  • Good! @TurjoyAhmed Thanks for accepting. Still I don't understand why it didn't work on your PC at first though. :) have a good day. – Andrew Doroshenko May 27 '21 at 14:20
  • Thank You. İ am going to write another post and İ will mention You in that post. For easier, İ will share the post link here. May İ do it? @AndrewDoroshenko – Turjoy Ahmed May 27 '21 at 15:05
  • Sure! @TurjoyAhmed – Andrew Doroshenko May 27 '21 at 15:09
  • I updated my question with new post but it has been closed. If You want I can continue here. Therefore, Now I want to summarize the index. As earlier, we are working on only dates. When If I insert OT then it will show the corresponding date. Now I want to find the result as if I insert OT then it will be showing corresponding date with values that contains the column. @AndrewDoroshenko – Turjoy Ahmed May 27 '21 at 19:07
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/232982/discussion-between-andrew-doroshenko-and-turjoy-ahmed). – Andrew Doroshenko May 28 '21 at 04:37
  • All right. İ am waiting for in the discussion chatbox @AndrewDoroshenko – Turjoy Ahmed May 28 '21 at 05:31
  • Sir, this is my new post link https://stackoverflow.com/questions/67738271/excel-formula-for-finding-the-month-end-overtime-summary?noredirect=1#comment119733013_67738271 @AndrewDoroshenko – Turjoy Ahmed May 29 '21 at 06:16