Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Time Difference when there are blank cells



Trying to calculate the total daily hours between start and end times.  The program I export the data has the log in/log out times in two different columns and there are numerous blanks between the two times.  I combined them into a single column, but I still cannot calculate the time difference because of the blank cells (cells aren't really blank, the formula changes them to "" if they do not meet the criteria).  Also, the number of blank cells changes depending on the person or the day.  

Attached an example.  

The last column has an example of what I'm trying to accomplish, but to be honest, I don't need it to look like that, i'm just trying to calculate the total hours. 




Blank cells wouldn't be so much of a problem. My immediate problem is that I can't tell from your question in which cell, on which of the several worksheets in your workbook, you want a total, nor which cells are supposed to be evaluated.

However, I saw that your times are text. It's possible to convert text to times but that doesn't make much sense since you took so much trouble to convert times to text before. Please [LINK URL="http://www.cpearson.com/excel/datetime.htm"]learn about dates and times here[/LINK]. You will also need to [LINK URL="https://www.excel-easy.com/basics/format-cells.html"]learn about cell formats[/LINK] and this is probably as good a site to do that as any alterantive.

The gist of it all is that, in Excel, what you see in the cell is different from what the cell contains. There is a Formula which produces Value, and that Value is displayed in a specified Format. Your formulas attempt to produce a Value that is equal to the display Format and that isn't possible where the display is text and the Value is a number. Dates and Times must be numbers because you want to do calculations with them. Therefore they can't be text.

You will learn enough at the two sites I have recommended above to advance your project a whole lot. However, I want to alert you to one trap that you might want to avoid. The nature of the trap is the difference between 11pm and 1am. That could be 22 hours or just 2 hours. The simple and easy way to get the correct resut is to include dates in your time. So, your calculation ends up like Tomorrow.01:00 - Today.23:00. Don't worry about the display format. Excel calculates in 24-hour terms but you can display the result any way you like, including AM/PM and without the date. If you don't want it to appear in the cell the cell format will take care of that. The important thing is to have it available in the cell's Value so that you can do correct calculations.


Answer the Question

You must create an account to use the forum. Create an Account or Login