SOLVED

Excel formula - Show value in dedicated cell based on various values in multiple cells.

Occasional Contributor

Hi,

 

I would like to get some help with finding a correct formula for the following cases.

 

Case #1

JTrehus_0-1643813272175.png

If text in A3 shows 'Monday' i would like to get the autosum of D3-C3 visible at E3.

But if A3 shows 'Sunday' i would like to get the autosum of D3-C3 visible at G3.

 

Case#2

But what if i work 1 hour overtime on a monday.....?

 

JTrehus_1-1643813864728.png

If text in A3 shows 'Monday' i would like to get my worked hours between 08:00 to 16:00 (normal hours) visible at E3 and 1 hour visible at F3 (from 16:00 to 17:00).

 

I hope someone can help me.

Have a nice day!

 

Jeroen

 

13 Replies

@JTrehus 

In E3:

=IF(A3="Sunday",0,MIN(D3-C3,TIME(8,0,0)))

In F3:

=IF(A3="Sunday",0,D3-C3-E3)

In G3:

=IF(A3="Sunday",D3-C3,0)

Fill down.

@Hans Vogelaar 

 

Good morning Hans,

 

Thanks a lot for your quick reply. 

The formula is working nicely, I only had to replace the comma (,) with a semicolon (;) in the whole formula.

 

JTrehus_0-1643877653953.png

 

Is it also possible to get the same results based on time of the day instead of amount of worked hours.

Example:

 

Normal hours: between 08:00 and 16:00.

Overhours 50%: between 16:00 and 21:00.

Overhours 100%: between 21:00 and 08:00.

 

Working on a Monday from 12:00 to 18:00 will result in 4 hours normal hours (E2) and 2 hours 50% (F2).

Working on a Monday from 12:00 to 22:00 will result in 4 hours normal hours (E2) and 5 hours 50% (F2) and 1 hour 100% (G2).

 

To make it even more complicated....

Is it possible to overrule these formula's based on date (column B).

Example:

Christmas is on a Wednesday, then all worked hours (any time of the day) need to be visible in column Overtime 133% (H3).

Meaning that 25.12.2022 and 26.12.2022 need to override all other formula's.

 

I know it's much more complicated then my first question, but i hope it is possible.

Have a nice day.

 

Greetings Jeroen.

best response confirmed by Sergei Baklan (MVP)
Solution

@JTrehus 

See the attached sample workbook. I used a list of public holidays in The Netherlands; you can edit the list as needed.

Hi Hans,

Thank you again (Hartelijk bedankt),

This is a amazing setup with formula's i never could figure out myself....
Thanks again for the quick and helpful support.

Have a nice day.
Jeroen

@JTrehus 

It may be possible but it won't be trivial.  Using 365 insider beta my formula was

= MAKEARRAY(2,4,
      LAMBDA(d,shift,
         LET(
            s,          INDEX(Start,d),
            e,          INDEX(End,d),
            wd,         IF(WEEKDAY(INDEX(Date,d))=1,1,2),
            shiftS,     INDEX(bandS,wd,shift),
            shiftE,     INDEX(bandE,wd,shift),
            laterStart, IF(s<shiftS, shiftS, s),
            earlierEnd, IF(e<shiftE,e,shiftE),
            hrs, earlierEnd - laterStart,
            IF(hrs>0, hrs, 0)
         )
      )
   )

I have used a Lambda helper function to produce all the results as a single formula but, using traditional Excel, it should be possible to pick the references out as relative references for each cell.  The Let would need to be replaced by a nested formula but that is not my scene.

image.png

@Hans Vogelaar 

Good morning Hans,

 

I have been 'playing around' with the file you submitted me.

Working nicely, also with the holidays.
But i still have some minor issues with some formula's. I tried to adjust some formula's but as this is far above my knowledge i had to give up....

Hope you can help me here one more time.

 

JTrehus_0-1643960571999.png

 

As long as the values in column C are below the max normal working hours (16:00), the table shows the correct values in the correct columns E, F, G and H. 
But if i insert a starting time above the max value for normal working hours, the result of the formula is a negative time, showing ##### in column E.

And at the same time the result of the formula calculating the overtime 50% in column F is showing a incorrect value.

I would like to get the automatic results in the overtime columns when starting after 16:00 and the column for normal hours showing 0.

I apologize if i haven't been clear from the beginning of this interesting conversation....

 

An other question is: How can i implement the Saturday also as overtime 100%.

 

Greetings Jeroen.

Hi,

Thanks for your reply.

Greetings Jeroen

@JTrehus 

Do you also need to take end times after midnight into account? For example

Start time = 20:00

End time = 04:00

If working over midnight (0:00) these hours need to filled out in a new row.
Example:
Working on 04.02.2022 from 20:00 to 04:00, the hours from 0:00 to 04:00 need to be filled out in new row with correct date.

@JTrehus 

Please check out the attached version.

@Hans Vogelaar 

Hi Hans,

Thank you so much.
This is exactly how i wanted to create this timesheet but as i wrote before, i could never figure this out myself....
I appreciate your quick response and excellent help...

 

Have a nice weekend.

Greetings Jeroen

@JTrehus 

Since this thread raised its head again, I attempted to match @Hans Vogelaar 's solution but restricting myself to a single dynamic formula and absolute references only.  I realise this is of no value to the OP and only a few readers will have the necessary version of Excel.

I started by using SCAN but came to the conclusion that I was going to need MAKEARRAY to display the resulting 'array of arrays'.  I therefore chose to switch to MAKEARRAY from the outset.  The result was a solution process that very much reflects the legacy spreadsheet strategy of developing a formula for a single cell and copying across and down the relevant number of cells.  The difference is that, rather than relying upon implicit intersection, I used a representative row and column index within the result array to determine the values to use within each calculation.  

I seem to remember that @Sergei Baklan regards MAKEARRAY as somewhat ugly, but I don't think it is that bad used when used to feed values into a Named Lambda function.  Something else that caused me grief with a single formula solution is that the early morning hours need to be combined with the evening hours when calculating hours worked at double time.  The mechanics of this calculation were to use MMULT but I chose to hide the formula step by defining a further Lambda function

CombineNightHrsλ.

= CombineNightHrsλ(
     MAKEARRAY(ROWS(Date),5,OvertimeHoursλ)
  )

image.png

With a bit of luck, by the time these functions come out of beta, I might know what I am doing!

At least, from Hans's sheet, I now know what the first and second days of Christmas are in Dutch; I never made it past 'dank je wel' before.