Need help with basic formatting and formulas

[JerusalemFriend]

It shames me to say it, as at one time I was very competent at/with Excel, but I’m trying to build a profoundly basic timesheet, and can’t seem to manage it without help!!!

I want to be able to enter 4 digits and have it appear as time, but when I use HHMM, if I don’t type in a Colon, all I get is 00:00 no matter what I enter. I want to be able to enter 4 digits and have the spreadsheet insert the colon.

I want to have start-time, end-time/finish, and Total Hours, but none of my attempts to subtract B3 from C3 have produced useful results?

[LeroyG]

1 is equivalent to 24 hours.

[erAck]

And a series of digits without any separator is an integer number.

[EarnestAl]

I suppose you could insert a column next to B3. In the new C3 enter =(LEFT(B3;LEN(B3)-2)+RIGHT(B3;2)/60)/24 and format it as [HH]:MM

[joshua4]

With EarnestAI’s approach you might want to use TEXT(B3,“0000”) instead of just B3…since otherwise an entry for 12:01 AM will be just 1 and break because of the LEFT(…,2).

The worst, though, is finding the differences. It is really easy to mess up a calculating timecard. I’ve attached a starting point…but as I said, it’s easy to mess up, for me included. My “one warning” would be to always include dates in your calculation…I suppose if you never have timestamps spanning midnight you might be okay, but you never know when business plans will change, and then you’d have a mess.

VeryBasicTimeCard.ods (21.5 KB)