I am trying to create an excel spreadsheet to record flexi time.?

I need the sheet to be able to calculate the hours and minutes worked after keying in the time, I have tried various ways but haven’t been successful, when I change the cells to time it asks for date as well, any tips would be appreciated.

10 Answers

Relevance
  • 1 decade ago
    Favourite answer

    It sounds like you are wanting to know the difference between two times to give you a total time worked. This is an easy, but can be a very difficult task. First the easy part.

    Cell A1 = Start Time (7:00 am)

    Cell B1 = End Time (4:00 pm)

    Cell C1 = Total Time (7:00 am to 4:00 pm equals 9 hours worked)

    In Cell C1 enter the following formula

    =b1-a1

    You will receive the result 9:00 AM. HUH!WHO, WHAAA NONONO!.

    First, right click on cell C1 and select the format of Number with 2 decimals and click ok. You will have the result "0.38". Now, in cell D1 enter the following formula

    =24*C1

    Cell D1 now reads "9", the total number of hours worked for the period. That's the easy part. Now the hard part.

    If your shift starts at 10 pm and ends at 7 am (ish), you are in for a world of work. I created a custom spreadsheet for time scheduling and tracking. I think that you can download a demo on my Google Groups. The problem lies in the fact that Excel will not calculate time formulas accurately because it does not recognize time that is not tied to a date. If you can't tie it to a date, you must go way roundabout to get it to work. I have about 45 hours into the application I developed, 30 hours in just the formulas of which 25 was spent on just this issue.

    You can check it out if you like.

    God Bless

    Frank Pytel

  • 4 years ago

    1

    Source(s): Phone Records Database http://reversephonenumberlookup.enle.info/?h9X8
  • Anonymous
    6 years ago

    This Site Might Help You.

    RE:

    I am trying to create an excel spreadsheet to record flexi time.?

    I need the sheet to be able to calculate the hours and minutes worked after keying in the time, I have tried various ways but haven’t been successful, when I change the cells to time it asks for date as well, any tips would be appreciated.

    Source(s): create excel spreadsheet record flexi time: https://shortly.im/jvzWS
  • Anonymous
    5 years ago

    You keep saying "Excel-like program". Is it Microsoft Excel you were using on Windows 98 or something else? If it's Microsoft Excel you were using then yes, newer versions of Excel will open your old files without any problems. If it's something other than Microsoft Excel you were using then it's 50/50 whether the newer versions of Excel will open them. If you can use your old spreadsheet program to save the files in CSV format (comma separated variables) then you should be able to open the CSV files in new Excel. But you'll lose all your formatting and graphs.

  • What do you think of the answers? You can sign in to give your opinion on the answer.
  • MLM
    Lv 7
    1 decade ago

    If you are looking for something that overlaps or covers multiple days, and want something really simple, you could set the columns to general numbering and use a 24 hour clock to calculate the time worked. For example:

    Column A

    0800 (This would represent 8AM)

    Column B

    1830 (This would represent 6:30PM)

    Column C (would be B minus A)

    1030 (This would represent 10 and a half hours worked)

    You could tweak the formula in Column C to subtract an additional 30 minutes to account for lunch breaks, etc. should you so choose.

  • 1 decade ago

    I just went to Excel, right clicked a column and 'Format Cells...'

    In there, I was on the number tab and in the Category box I selected Time.

    I chose the time format I wanted.

    I repeated this on a second column.

    I entered two values for time in the 24 hour clock and subtracted them successfully.

    I am using Excel 2003.

  • 1 decade ago

    If you enter in the time in format HH:MM, Excel should recognise it as a time.

    If you subtract two time values then Excel should give you the difference in another time value (e.g. 15:30 - 11:00 = 04:30)

    If you want more control, you can use =Minute(A1) to get the minutes of cell A1 and =Hour(A1) to get the hours. When you do that, be careful to set the format of the cell to something like General, and not Date or Time. You could then use something like

    =(HOUR(B1)*60+MINUTE(B1))-(HOUR(A1)*60+MINUTE(A1))

    to get the number of minutes between. Be careful with the formatting though - check it's on "general" for the minutes cell!!

    Finally, if you're really having trouble entering the time value and having Excel recognise it as a time ... if yu have the hour in A10 and the minutes in B10, you can use =TIME(A10,B10,0)

    ... that's assuming you're not timing them by the second!!

  • Anonymous
    1 decade ago

    Go to http://www.mrexcel.com/ register (it's free and easy), then post your question there. It's a great site with lots of experts on hand to help sort your problems. They're usually very quick to answer too.

  • 1 decade ago

    Must use a colon: as hour minute separator, if you use a . then the date is included

  • Anonymous
    1 decade ago

    I use the one here http://www.spreadsheetsdirect.com/examples_flexi.h... which is free to download.

Still have questions? Get answers by asking now.