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 5 hours 34 minutes ago

If you mark half-lessons with an asterisk in the cell with the times, you change the times to text values, which makes it difficult to perform calculations.

Without the asterisks, the earliest time in say B2:B100 is =MIN(B2:B100) and the latest time + 30 minutes is =MAX(B2:B100)+TIME(0,30,0)

The time between the earliest time in B101 and the latest time in B102 is =B102-B101.

If you want to count the number of lessons in B2:B100, use =COUNTA(B2:B100)

The above formulas can be filled to the right.

The number of lessons in a row, say in B2:Z2, is =COUNTA(B2:Z2) if you want to count both text and number values (times count as numbers). If you only want to count number values, use =COUNT(B2:Z2).

These formulas can be filled down.

That's wonderful, thank you! Thank you for bearing with me with what are probably rather basic questions! 

OK, I appreciate that asterisks make things more complicated. Perhaps I should indicate half-lessons (which are very rare) in a different way, without messing up the use of times. Do you have a suggestion that is Excel-friendly? 

Also, in some cells I use two times because I teach two lessons that week. What do you think about this? 

(I appreciate this is not really Excel but more user decisions.)

There are some other calculations I want to do but until I change my text sheets into time sheets I don't know whether my formulas need tweaking. 

On that subject, is there a way to alter text to times without it becoming gobbledegook?

For optimum flexibility, I would enter the lessons in a database table-like format:

You can then use a pivot table (or multiple pivot tables) to generate overviews, such as number of lessons, total duration of lessons etc.:

It would also be possible to use formulas to create a calendar-like overview.

See the sample workbook at https://www.dropbox.com/s/avy2xv5q42brfxw/Lessons.xlsx?dl=1