I :slash: ❤ Excel...

Factual Questions
1

…but I still gotta use it.

For some reason, the IT people at work don’t think we need anything more substatial than a spreadsheet app to do our database/scripting work. Assholes.

Anyway, can sombody tell me what the hell is wrong with this function?

=(TIMEVALUE(C3) - TIMEVALUE(B3) + TIMEVALUE(E3) - TIMEVALUE(D3) + … TIMEVALUE(U3) - TIMEVALUE(T3))

or hell, just

=TIMEVALUE(A1)

When I enter individual cell keys in the function wizard, it works just fine, but when I actually enter the function into a cell, all I get is #VALUE!. I’ve tried it with quotes, without quotes, and every other f’in way I can think of.

What gives?

And for the record, Excel just crashed. Before I saved the exquisitely formatted 966-cell table that I need this function for. Marvelous.

Grrr…

2

TIMEVALUE requires a text argument. e.g. if B3 contains =“7:17:00 PM” it will work. If it contains 7:17:00 PM it won’t (because excel will treat as a serial date)

3

btw, what are you trying to do?

4

Somebody volunteered me to write a weekly stat sheet for the some workers at my job.

They need to account for every minute of their time, so I’ve got something that looks like:



                From   To      From   To    .... Total.
Shelving    8:00    8:45   10:30  12:15
Barcoding    8:45    9:20
Sorting    9:35    10:30

That probably won’t come out right, but you get the drift.

They enter the times they were doing each job, and I get the total number of hours they spent doing each job for a day. I’m using TIMEVALUE in the daily total column.

I then take that daily time count and do various things with it, which is the easy part.

[geek]This would be a helluva lot easier with a Unix timestamp.[/geek]

I’ll give your suggestion a shot. I’ve only got OpenOffice Calc at home, so it’ll have to wait until Saturday when I go in next.

5

:smack:

=(b1-a1+d1-c1)*24

:smack:

6

I think you’ve got it!

Yes - Excel stores dates and times as date serial numbers: these are days since Jan 1, 1900. Times are fractional parts of days, e.g. .5 = noon.

7

I used to use an Excel spreadsheet to keep track of work hours. The columns in which the start and finish times and hours worked were formatted with the Custom format hh:mm ). The work time in hours and minutes e.g 7:50 was derived just by =a1 - b1.

8

If it didn’t work I have another suggestion. I remember having a hell of a time trying to figure out a similar situation. No matter what I did I kept getting something strange like what your getting. Turned out to be the stupidest thing. THE COLUMN WASN’T WIDE ENOUGH. It had to display something like $1234.34 and was only wide enough for part of the number and I ended up getting “####” Took me FOREVER to figure that out.