On Time Every time

understand how Excel stores dates and time. Date and Time 101 Excel stores time and dates as serial numbers so in this case what you see is not what is in the cell. Only When you format the serial number as a date or time, or date and time as in the example below, it displays it in a date/time format but underlying is still a serial number. The serial number consists of two parts. The Serial Number The digits before the decimal comprise of  the date and the digits after the decimal make up the time. Dates in Excel Dates in Excel start from the  1st January 1900. The serial number Therefore for 1/1/1900 is 1. The serial number for 1/1/2012 is 41640 -  because it is the forty one thousand, six hundred and forty days since 31/12/1899*. Note: in the uk our dates are displayed as dd/mm/yyyy. USA mm/dd/yyyy and Germany uses the full stop instead of / or - *Actually 1/1/2012 is only the 40908 dayss but Excel includes the date 29th Feb 1900 even when the year 1900 was not a leap year. The inclusion was intentional to provide compatibility with Lotus 1-2-3 which contained a bug and was the market leader when Excel was released! Time in Excel Time serial numbers represent a fraction of a 24 hour day. Convert Time to Decimals we need to convert time to a decimal on a regular basis so that we can calculate hours x rate for the purpose of payroll or billing. Remember, the serial number is a fraction of a day so simply multiply by 24. Note: you don’t have to enter a date and time. If you enter time only, the date part of the serial number is 0. We have the rules let’s work on some examples working with time. Shift Work Timesheets and Overtime Calculating the difference between two times on the same date is as simple as subtracting the start time from the finish time, but it’s not so easy if your start and finish times are on different dates, as in the case of shift workers. Notice the finish time below for Monday is actually 7AM on Tuesday. We can use a clever trick to test for time that finishes on a different date by checking whether the finish time is less than the start time, as is the case for Monday and Tuesday above. Taking the formula in cell G4: The first part of the formula takes the finish time less the start time and then checks whether the finish time is less than the start time (E4<B4). In the case of Monday (E4<B4) evaluates to TRUE, and since TRUE = 1 it adds 1 to E4-B4 to correctly calculate the time. Note: if your times are entered with the date and time you can simply subtract one from the other, it’s only in the case where times are entered on their own that you need to test whether the finish time is < the start time. Simple Time Sheet Type the week start date in cell C3, the Week beginning. Use the format dd/mm/yy, the name of the day will appear automatically. The date is then passed down to the Day column. Type the amount of hours you are expected to work in G3, the Normal Hours. This is used later to calculate if have worked over or under the required hours. Type the times you arrive and leave work in the appropriate columns. Use the format of hh:mm. Heres The Formulas revealed Note The Total Hours cell has been formatted as [hh]:mm. This ensures the total hours can be expressed as a value above 24 hours. If the [hh]:mm format had not been used the Total Hours would show as : 14:45 If the [hh]:mm format does not show in the cell format dialog box on your computer, it can be created using Format, Cells, Number, Custom.     Rounding Time Often solicitors need to round time in 10 minute increments for the purpose of billing clients at an hourly rate. The table below shows rounding using the ROUNDUP, MROUND and ROUND functions. If you want to bill in 30 minute increments change the 10 in the above formulas to 30. Display Time with Text How to display time in a format that reads ‘2 hours 15 minutes’. There are quite a few formulas that will concatenate text for the words ‘hours’ and ‘minutes’, but I prefer to simply use a custom number format. You can see in cell K2 the formula subtracts the start time from the arrival time to give the number of hours late. I then formatted the cell to show the time with words using a custom number format like this: The benefit of this approach is that the underlying time value remains in the cell so you can use it in other formulas. For example you might like to add up the Time Late column to get a total time late etc

Excel can be fun try out some of its features Detailed on this Site

Mike Barrett (MCT) Senior Training Advisor