Excel as Work Schedule

B

BoMead

New member
Joined
Jul 21, 2017
Messages
2
Reaction score
0
Points
0
I use Excel for a work schedule. Column A is the list of employees. Column B to AF is the number of day of the month (ex B is day 1, K is day 10, and so on). The hours the employee works is represented by a letter; D=12 hrs, N=12hrs, d=8hrs, s=8hrs, and m=8hrs. I would like a formula that can change the letters to the hours then sum up the hours for the end of the month, in column AG would be the total of hours the employee worked for the month.

1st 2nd 3rd 4th 5th 6th 7th
Emplyee1 D d s D Total=40 hours


note employee didn't work 5 days straight and the different letters means a different time of day, so I cant just use numbers.

Any help is greatly appreciated.
 

Attachments

  • Example.xlsx
    8.5 KB · Views: 23
you could try in K3, array-entering (use Ctrl+Shift+Enter instead of just Enter when committing the formula to the sheet) this formula:
Code: 
=SUMPRODUCT(IF(EXACT($B3:$J3,"N"),12)+IF(EXACT($B3:$J3,"D"),12)+IF(EXACT($B3:$J3,"d"),8)+IF(EXACT($B3:$J3,"s"),8)+IF(EXACT($B3:$J3,"m"),8)+IF(EXACT($B3:$J3,"O"),8)+IF(EXACT($B3:$J3,"d^"),8))
and copy down.
It's cumbersome, but your requirement is case-sensitive, so other simpler formulae I tried didn't differentiate between D and d.
(I don't know what to do with d/O so I've ignored it.)

If I were to say at this point that there is no simpler (worksheet formula) solution, then I'm sure this would prompt someone to produce one - unfortunately, there probably is a simpler solution!

edit post posting:
…and there is:
Code: 
=SUM(IF(EXACT($B3:$J3,{"N";"D";"d";"s";"m";"O";"d^"}),{12;12;8;8;8;8;8}))
array-entered again.

ps. if there's a danger of someone entering leading/trailing spaces on the sheet then:
Code: 
=SUM(IF(EXACT(TRIM($B3:$J3),{"N";"D";"d";"s";"m";"O";"d^"}),{12;12;8;8;8;8;8}))
will help
 
Last edited:
Thank you for the help, it is greatly appreciated. Can I ask what Ctrl+Shift+Enter does?
 
You must log in or register to reply here.
Back
Top