Data Format Changing Back To Automatic

Question

Level 1

17 points

Data Format Changing Back To Automatic

I am trying to create a timesheet calculator and am running into multiple issues.I keep changing a cell's data format to time, but as soon as I type anything, it goes back to automatic.

1) Why does the cell's data format keep changing back to automatic?

2) Is it possible to have a spreadsheet that I can type in hours such as "7:08 pm" as "7.08" and have it convert to time if it's in a cell with a time format?

3) What's the best way to create a time sheet? I need to have a time in and time out -30 mins for lunch and then the total as a decimal.

I have attached a picture of the automatic data format issue and a picture of what exactly I'm trying to do, if anyone has a better solution...

iMac, OS X Mavericks (10.9.5)

Posted on Jul 17, 2015 3:16 PM

Reply

Answer 1

Yellowbox

Level 7

21,373 points

Jul 17, 2015 7:00 PM in response to The Tree Dr

Hi Tree Dr,

Here is one idea.

Enter hours.minutes in Column B

I have used the 24 hour clock instead of am pm

Formula in C2 (and Fill Down) =INT(B2)

Formula in D2 (and Fill Down) =(B2−C2)×100

Formula in E2 (and Fill Down) =C2&":"&D2

Formula in F2 (and Fill Down) =C2+(D2÷60)

The Display in E is text and can't be used in calculations.

You can hide columns.

The example 12.8 to 12:08 is beyond me. Type 12.08 instead

Regards,

Ian

Reply

Answer 2

SGIII

Level 8

36,284 points

Jul 17, 2015 7:45 PM in response to The Tree Dr

The Tree Dr wrote:

3) What's the best way to create a time sheet? I need to have a time in and time out -30 mins for lunch and then the total as a decimal.

Have a look at the Employee Schedule template (File > New in your menu):

SG

Reply