I want to be able to add a value to a column that depends on the day of the week.
To simplify what this is for, say it is a monthly timesheet for someone who works different hours on different days of the week. It doesn’t always start on the same day of the week.
I have a column (A) with dates in a column (fill series, weekdays). I currently have another column B which is = column A but has the date format DDD - so I know what day of the week it is.
I have another column C which is for hours worked.
I currently enter the hours worked on each day for the first week and then copy and paste it for the rest of the month.
I’d love to be able to use, say some Weekday/If based formula, in column C so when I enter the dates in A the relevant value (no of hours) appears in column C.
I’ve had a go and I can’t get it work…any suggestions?
See function WEEKDAY() - LibreOffice Help - WEEKDAY and use something like IFS(WEEKDAY(A1)=1;<somecalculation>,WEEKDAY(A1)=2;<someothercalc>;WEEKDAY(A1)=3;....)
It’s not very difficult, just don’t use six or seven nested IF().
You will need two functions - WEEKDAY() and CHOOSE()
WEEKDAY(<cell with date, your column A or B>;2) - second parameter 2 will return 1 (Monday) through 7 (Sunday).
CHOOSE(<value for choose - from 1 to N>;<value for 1>;<value for 2>;...<value for N>)
So, formula for cell C3 will be
=CHOOSE(WEEKDAY(A3;2);<for Monday>;<for Tuesday>;<for Wednesday>;<for Thursday>;
<for Friday>;<for Saturday>;<for Sunday>)
For example
=CHOOSE(WEEKDAY(A3;2);8;4;0;8;4;0;6)Thank you! This worked perfectly…I was doing lots of ‘IF’ and getting errors or the wrong value…