Excel Formula to count the number of night hours (18:00-06:00) over a 2/3 day period

Hi,

Please don't take this the wrong way but you are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it, Yours exhibits all those features.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

I recommend you change your layout so you have columns for:

1. Date.
2. Day (Optional since you can always format the date column to display say "Sunday 1 July"
3. Locations. (Drop down box containing 'DUR', 'JNB' etc. and 'Office'
4. Depart / Arrive (A drop down box which lets you select either "Depart" or "Arrive"
5. Time

In addition if this is a time sheet for one of many individuals then I'd add a 6th column, make it the first column, which holds a drop down with peoples names.

Then in a separate table hold all the individual names along with their 'Normal daily hours'

If you do this you will have a 'proper' database which then lends itself to straightforward analysis with simple functions and standard filtering/sorting functionality.

If you do this upload the new workbook and we can no doubt help further