Any Excel spreadsheet format experts in the house?

Factual Questions
1

Here’s my problem. It is called SAM II. Our timekeepers enter overtime into the SAM II system on an hours and minutes basis - for example: If you work an hour and 15 minutes over, your timekeeper will enter 1.15 hours. Half an hour is .30 hours, etc.

If you earn time and one half, SAM II calculates this for the timekeeper and rounds up to the nearest minute. So if you work 15 minutes over, you will receive 23 minutes.

This becomes a problem when the timekeepers are also expected to keep a seperate overtime liability spreadsheet. They now must calculate time as a percentage of an hour. This creates all kinds of confusion, especially when they call me and ask me to find their error. (You want me to what? :confused: )

What I’m looking for is a custom format I can put in the spreadsheet that will allow them to enter time the same way the enter it into SAM II.

One format suggested to me is [h]:mm. The problem with this is if you enter 1.15 into this it interprets it as 1/1/1900 3:36:00 AM and displays it as 27.36. If you enter 1:15 it gets the display part right, but I forsee mass confusion with it interpreting either one as a number but only one of those being correct.

Can anyone help me? Please? Or am I doomed to live with this horror for the rest of my days? :smiley:

2

I am not sure I understand but you can enter 1 hr and 15 mins as 1.15 in one cell and this function will convert that to 1.25 hrs: =INT(RC[-1])+(RC[-1]-INT(RC[-1]))/0.6

3

I think sailor’s solution is the right idea. Keep apples as apples and oranges as oranges. So, have a place in the spreadsheet where stuff is input in SAM II time, another where it’s processed as proper decimals, and another where it’s output in SAM II format.

Also, FYI, the reverse translation is =int(a2)+(a2-int(a2))*.6 (where a2 is the cell to be translated back to SAM II)

4

Okay - I got it and it is working. Thank you both very much!