1

I am trying to calculate the salary for employees in Google Sheets.

Night Shift - if shift start time lies between 12 am-6am
Day Shift - if shift start time lies between 6am-11:59pm

I am able to calculate the salary for night shift employee(D7), I dont know to what am I doing wrong, but I cant seem to add more conditions like day shift and if employee present.

my requirement is:

if employee present & Night shift then 500  
if employee present & day shift then 400

=ARRAYFORMULA(IFERROR(IF((TIMEVALUE(A8)>=TIMEVALUE("4:00:00"))(TIMEVALUE(B8)< TIMEVALUE("10:00:00")), 250,IF((TIMEVALUE(A8)>=TIMEVALUE("06:00:00")) (TIMEVALUE(B8)<=TIMEVALUE("23:59:59")), 225 && IF(REGEXMATCH(C8, "P|p"), 225, 0))))

any help is appreciate. Here is the link for the Google sheet I am trying.

Here is the formula I am tryin:

player0
  • 124,011
  • 12
  • 67
  • 124
Ophelia
  • 73
  • 9
  • As far as I know, you can't do `&&` in your cell, but you should use `AND(condition1, condition2)` – Neo Oct 10 '22 at 07:53
  • what is difference between `12 am-6am` and `11:59pm-5:59AM`? Only 1 minute? So, when shift starts at `5:00 AM` then what shift it will be? – Harun24hr Oct 10 '22 at 08:17
  • 1
    oh shoot! I meant to write 12am-6am as night and 6am-11:59pm as day shift – Ophelia Oct 10 '22 at 08:25

2 Answers2

1

Try XLOOKUP() function-

=INDEX(XLOOKUP(A7:A9,{TIME(0,0,0);TIME(6,0,0)},{500;400},,-1))

For dynamic spill array use BYROW() function.

=BYROW(A7:INDEX(A7:A,COUNTA(A7:A)),LAMBDA(x,XLOOKUP(x,{TIME(0,0,0);TIME(6,0,0)},{500;400},,-1)))

  • Here A7:INDEX(A7:A,COUNTA(A7:A)) will return a array of values as well cell reference from A7 to last non empty cell in column A (Assume you do not have any blank rows inside data). If you have blank row, then you have to use different approach. See this post by @TheMaster

  • Then LAMBDA() will apply XLOOKUP() function for each cell of A column.

  • XLOOKUP() with match_mode -1 indicate to lookup shift time or next lower value. Means, when you looking for shift 5:00 AM, it will match with 0 means 12:00 AM and return 500. Otherwise will match to 6:00 AM and return 400 as day shift starts from 6:00 AM.

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • Appreciate your time @Harun24hr, but this does not work. The present/absent condition nor the shift start time logic is picked up by the formula. – Ophelia Oct 10 '22 at 12:15
1

try:

=INDEX(IF(C7:C="Present", IFNA(VLOOKUP(A7:A, 
 {0, 500; "6:00"*1, 400; "12:00"*1, 500}, 2, 1)), ))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • thank you for suggesting an answer. I tried the formula, but the shift time does not change in case of the day shift. https://docs.google.com/spreadsheets/d/1XutUKvhnA1I74Z9RebnmTAOATarYWCiBWNj12yOPwic/edit#gid=0 – Ophelia Oct 10 '22 at 12:13
  • @Ophelia answer updated. try now – player0 Oct 10 '22 at 12:55
  • Thanks again! I have accepted the answer. I just realized that I didn't noticed I have been using AM/PM in my dropdown list of my shift selection and if time is after 12PM the amount is taking as 500(as if its for night shift). What am I missing in this formula? https://docs.google.com/spreadsheets/d/1XutUKvhnA1I74Z9RebnmTAOATarYWCiBWNj12yOPwic/edit#gid=0 Two things: considering AM/PM in my dropdown & the actual night shift itself is not considering. I know I have been pestering too much. Any help is appreciated and yet let me know where to look for an answer. Thank you so much – Ophelia Oct 10 '22 at 23:50
  • @Ophelia yeah those AM/PM times of yours gets confusing so if you could clarify it ( exact times for shifts and their points) it would be helpful to fix the formula to act exactly as you wish. lets try this: there are 2 shifts Night and Day. Night is from 00:00 to 06:00 and Day is from 06:00 to 12:00 ? is that correct? so from 12:00 to 00:00 midnight is what? – player0 Oct 11 '22 at 01:12
  • Each shift is 3 hours. It's part-time voluntary work. night shift - any shift starts between 12:00 AM till 6:00 AM. e.g: 12AM to 3AM, 4AM - 7AM. - Rs.500 day shift - any shift starts between 6:00 AM till 11:59 PM. - Rs.400 – Ophelia Oct 11 '22 at 01:29
  • @Ophelia is this correct? https://i.stack.imgur.com/mPUxL.png – player0 Oct 11 '22 at 02:13
  • yes, the above screenshot is correct. Perhaps use this sheet for reference and trying? https://docs.google.com/spreadsheets/d/1XutUKvhnA1I74Z9RebnmTAOATarYWCiBWNj12yOPwic/edit#gid=0 – Ophelia Oct 11 '22 at 15:48
  • @Ophelia in that case formula is: `=INDEX(IF(C7:C="Present", IFNA(VLOOKUP(A7:A, {0, 500; "6:00"*1, 400}, 2, 1)), ))` – player0 Oct 11 '22 at 18:22
  • I hesitated to ask this yet did it for a quicker turnaround. I thank you in advance for this. I wanted to one more scenario to the formula. I have introduced a new column D. If IDs used less than or equal to 40, then half the pay. i.e, day shift scenario: >40 Ids = 500, <=40 ids = 250 for day. Night shift scenario: >40 Ids = 400, <=40 ids = 200 for day. I tried to achieve this and failed in D4. Can you please guide me? https://docs.google.com/spreadsheets/d/1AUz-2cdoBiZGFoO1KhJUtgUK-MCBLJun3f3S3P6DlOg/edit#gid=1844257360 – Ophelia Oct 19 '22 at 06:23
  • @Ophelia sheet is private – player0 Oct 19 '22 at 09:48
  • I am sorry, I have made it public! https://docs.google.com/spreadsheets/d/1AUz-2cdoBiZGFoO1KhJUtgUK-MCBLJun3f3S3P6DlOg/edit?usp=sharing – Ophelia Oct 19 '22 at 11:59
  • 1
    @Ophelia try like this: https://i.stack.imgur.com/ReK4m.png – player0 Oct 19 '22 at 16:50