1

I'm specifically looking to fill the cells K2:K10 with the hours J Bloggs would have worked if it weren't a bank holiday.

enter image description here

The screenshot shows J Bloggs working 9.5 hours for three days a week starting April 1st. In October he decides to change his hours to 7.5, five days a week. Finally on the 12th of Jan he changes his hours to 5 hours a day mon-fri.

I'd need the cells in column K to look up the right hours on the right day of the week between the correct dates in A5:A8 (and H2).

Note: A1:A2 is irrelevant here. Also I'm aware the dates I've hastily added for bank holidays are incorrect ones for 2017/18.

WillacyMe
  • 562
  • 1
  • 6
  • 25

2 Answers2

0
  1. Open your Excel spreadsheet. Insert the title "In" in cell A1 and the title "Out" in cell B1. Enter the start time in cell A2 and the end time in cell B2.
  2. Enter the title "Total Hours" in cell C1. Select cell C2 by clicking on it with your mouse.
  3. Enter the formula =B2-A2 and press the Enter key to display the time difference between two cells. Change the cell format to hours by selecting the cell and clicking on the arrow next to the "Number" option, which is on the "Home" tab. Select the "Custom" option, and type "h" into the "Type" field to change the cell format to hours. Enter the formula =TEXT (B2-A2,"h") to do this directly within the formula.
  4. Count the total number of hours worked based on a single time unit by entering the formula =INT((B2-A2)*24) and then pressing the Enter key. The INT function enables you to count the total number of hours between two times and round down the total to the nearest hour.
Peter Badida
  • 11,310
  • 10
  • 44
  • 90
Etoh Kennedy
  • 1
  • 2
0

I may have answered my own question. Using the algorithm below

=VLOOKUP(J3,$A$5:$H$8,MATCH(TEXT(J3,"ddd"),$A$4:$H$4,0),TRUE)

The answer I get here would be 9.5 when entered into cell K3

Please do post if you have a better way about going about this.

WillacyMe
  • 562
  • 1
  • 6
  • 25