Excel Help, please.

I’ve been trying to get a spreadsheet at work to function and have run into this problem. I need to compute the hours worked for a single shift based on the time entries in two other cells. I can’t seem to get it to work. Excel help says to use a formula like: =TEXT(A:3-B3:“H:MM”)

This is from memory, so there might be a minor error, but you get the idea. When I actually programmed the cell, I had the “help” page open, so I formatted it exactly like the example. Unfortunately, I get a “value” error from the cell. I thought about just using integers for all the cells, but I work in the casino business, so the origin cells need to possess date and time data, since shifts pass over midnight (for example, 10pm to 6am shifts). I’ve tried all kinds of different formats for the origin cells to no avail. Is it possible that the help function is giving advice that is not applicable to my current iteration of Excel?

Thanks in advance, teeming millions! :slight_smile:

Exactly what data is in the two cells? Is it just time? Or is it date+time? If it is just time then a subtraction will give you incorrect results (although not a #VALUE error). If it is date+time then it ought to work with this formula:

=TEXT(B1-A1,“hh:mm”)

It would also work with this formula

=B1-A1

if you format as “Time”.

I can’t see anything about what you’ve described that will give you an error of any kind. Feel free to email me your file if you’d like.

For this particular question it shouldn’t make a difference what version of Excel you are using, but you should always specify that when you ask a question.

I also have had very good experience in posting Excel questions to http://www.excelforum.com/ The board requires registration, but it’s free. And you can attach sample files up to 1M for an Excel file and over 9M for a zip file. I post there as 6StringJazzer.

If you put the date in each cell, the formula works.