Hi all

I have been using Excel for a while, but only recently getting into more complicated functions. In particular, I'm only just wising up to using actual dates instead of text substitutes. 

At present, Im using Excel to build timetables I teach piano in slots through a school day week by week. 

Heres a description of my spreadsheet:

Down the left column I have the names of my pupils. 

Across the top I have the dates of the lessons. 

And in rows I have the times of each lesson. 

So, reading across the rows gives the times of lessons for each pupil in different weeks: 11:00, 14:30, 14:00, 12:00, etc.

And reading down the columns gives the times of the lesson slots for every pupil in a given week: 10:30, 11:00, 11:30 etc. 

I would like to have some help with the following: 

1) First of all, I would like Excel to tell me in a separate row at the bottom what the earliest time and the last time is in each column. In other words, Id like it to inform me my start time and end time for each day. Its important to note here that the final lesson, say, 16:00, would end at 16:30, so I need the final time plus the lesson time added. 

2) Then Id like to know how many hours and minutes that working day lasted. 

3) At the RH side Id like to know how many lessons were given, so if there are time slots in 9 columns I need the figure 9 to show. In an another version of my spreadsheet the times are given as text, so Im using COUNTIF to count the colons in the middle of the times. However, I cannot get COUNTIF to count the colons in the times (perhaps theyre really decimals?). So, the question is, how can I get Excel to count the number of lessons given. Bear in mind not all the columns will have times in as its possible pupils might miss certain weeks. 

4) Sometimes I give half-lessons and mark with an asterisk. I would need the final column to show 9.5 in this scenario and of course the bottom row to take account of a missing 15 minutes. 

I hope all this makes sense. And I hope someone can help. 

• Moved by Lanlan HuangMicrosoft contingent staff Monday, March 30, 2015 1:52 AM

Thanks for this. 

Looking at each term, the first one was displaying MIN correctly but the second one was not. 

The first term's times were entered 00:00:00 but displaying 00:00. 

The second term's times were entered 00:00 and displaying 00:00. 

Why does the first term find MIN and MAX accurately but not the second? 

Without seeing (a stripped-down copy of) your workbook, it's impossible to tell what's wrong.

How do I show a stripped down copy? Can I send it to you for suggestions? 

Also, I am trying to calculate rate of pay per hour by dividing earnings (format: currency) by time (format: [h]:mm). However, the value is not correct. What do I need to change? 

You might create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as Microsoft OneDrive (https://onedrive.live.com), FileDropper (http://filedropper.com) or DropBox (https://www.dropbox.com). Then post a link to the uploaded and shared file here.

Or register at www.eileenslounge.com (it's free) and start a thread in the Excel forum. You can attach files up to 250 KB to a post there (zipped if necessary).