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...

User uploaded fileUser uploaded file

iMac, OS X Mavericks (10.9.5)

Posted on Jul 17, 2015 3:16 PM

Reply
2 replies
Sort By: 

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

Hi Tree Dr,


Here is one idea.

User uploaded file

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

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Data Format Changing Back To Automatic

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.