Excel/Excel problem


I have created a time table in excel where consists of 31 rows representing 31 days of a month. Each row consists of 48 cells representing every "half hour". That means that I have 48 cells in every row for each day (24 hours period).

With "X" I mark the days of work and I have the following conditions which I must satisfy:

1) Hours of rest shall be at least 10h in any 24h period
2) Hours of rest may be devided into no more than two periods one of which shall be at least 6 hours in length in any 24 h period
3) Working hours shall be max 14 hours in any 24h period

4) "Any 24h period" means that the above conditions shall be satisfied from example from 00:00 to 24:00 of any day but also for example from 02:00 of one day to 02:00 of the next day or 17:30 of one day until 17:30 of the next day

If all the above are satisfied the excel should so as result "Hours of rest are fine"

If one of the above is not satisfied then relevant message indicating which condition is not satisfied should be depicted.

Can you pls help me on this issue??

Thanks in advance


Hi Petros

How are you?
I think the easiest way to solve this is>
- Check each condition for each row

type in Aw1

Type in Ax1
Type in Ay1

=COuNTif(A1:AV1;" ")<=14

Type in AZ1

- Then add an extra condition in BA1
=And(Aw1,Ax1, Ay1, Az1)

I hope this helps.


About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Adelaide carvalho


I will be able to answer any question on Excel and Visual Basic for Applications - class modules, recursive procedures and functions are my favourite


Past/Present Clients
Several readers of my Books think they are useful.

©2016 About.com. All rights reserved.