Users are saying about us...

I flew in the RAF for 27 years and have recently tried to audit my logbook additions. The times are in Hrs and 5min increments. It is cumbersome so I was interested in this tip. However, the wrong spreadsheet appears to have been added to this tip. It is the same as for the previous tip and does not relate to the text describing the tip. e.g. cells E4:E8 is hh:mm. Do you have a way of adding columns of hrs and minutes?

I need to work out difference between 2 dates and times in excel, but in working hours only, ignoring off duty hours (Weekends are working days). i.e working hours are 0800 to 2000 – start date/time 21/04/03 09:00 end date/time 22/04/03 17:00 – but for some rows the actual start time is prior to 08:00 and on others the end time is after 20:00 so the formulas I have tried so far havent worked properly – can anyone help?

Is there an easy way to convert hours to read as standard time rather than military time? I am automating time sheets for my emplyees and I am finding it difficult to show 4:00 PM rather than 16:00 PM. -anyone?

“Here is my decision:
1. write in column A (for example) hours listed (i.e. 1:00, 1:15, :45, etc.)
2. in cell B1 (or any other you want) write this formula: =VALUE(SUBSTITUTE(TEXT(A1;””HH.MM””);LEFT(TEXT(A1;””HH.MM””);3);””””))+VALUE(LEFT(TEXT(A1;””HH.MM””);2))*60
3. Copy & Paste the formula as much as it is necessary
If you have any problems or questions – write to my e-mail: koliu_m@yahoo.com or ICQ: 56468899″

im trying to write a formula that calculate the difference between the start and finish of my staff as well as taking the break time off the total … eg 9 hours work = 8 pay, 6 work 5.5 pay … any ideas ??? THANK YOU

I have a spreadsheet that I’d like to show times that include minutes as 1/4 of an hour, or 1/2 of an hour. EG – 5:15 shows as 5.25 hours; 5:30 shows as 5.5 hours; 5:45 shows as 5.75 hours Thank you very much for your help.

“Lori,
You have to explain to Excel that one day has 24 hours. So, first you have to format the cell as [h]:mm. It will show 5:15 in cell A1, for instance. Then, in cell A2, you multiply A1 for 24, and you will have 5,25 hours. Note that the format in cell A2 is general number.
This tip can also be very helpful for those who need to calculate timesheets, employee payments, etc.”

“I am working on a spread sheet that I need to calculate an average time it takes to do something (in minutes) (i.e. Number of Trays Total / Average Trays Per Minute = Minutes it Takes to Fill Trays).
Next, I need to add the mintues to a time(i.e. 6:30 AM + 5 minutes (from calculation above) = 6:35 AM).
Finally I need to add more minutes (time it takes for someone to push a cart from kitchen to hospital unit) to the time we just got (i.e. 6:35 AM + 5 Minutes = 6:40 AM).
Thank you,
Chad.”

I am working using functions IF statements and I need to be able to calculate the duration between different times. Day Rate = 08:00 – 18:00, EVE Rate = 18:00 – 08:00 and Wkend Rate = Midnight Fri to Midnight Sun. So for a call starting on Thursday 17:00 and ending on Friday 19:00 would be charged for 1hr at day rate and 1hr at evening rate. How can i display this in Excel. I can break it down into many cells as poss if need be. I am finding this impossible, any experts out there

“Hi Joe,
All you have to do is subtract one from the other.
For example, using your dates / times above:
B2 contains 6/1/2003 07:00
B3 contains 6/2/2003 13:00
The formula to get the difference between the two is just:
=B3-B2
Obviously the answer we want is one month, 6 hours, and this formula returns 31.25 days (= 31 days, 6 hours).
A month is not a well defined time period (28, 29, 30, or 31 days) so best to avoid ever using it if you can.
The answer of 31.25 days is correct, but most people would prefer 31 days, 6 hours.
To get the cell to display the answer in that *format* (and show minutes for example), just apply the following custom format to the cell:
dd”” days””, h”” hours””, m”” mins””
If you wanted it displayed in just hours (750 hours), use the following format:
[hh]:mm
Hope that helps,
Alan.”

“Hi Chad,
The basic trick here is to remember that Excel likes to work in days (being equal to 24 hours) as its default time unit.
>I am working on a spread sheet that I need to
>calculate an average time it takes to do something (in
>minutes) (i.e. Number of Trays Total / Average Trays
>Per Minute = Minutes it Takes to Fill Trays).
Okay – to calculate the average time that it takes to do something:
A1 = Total time = 2 hours = 2/24 of a day = 0.083333….
A2 = Total ‘events’ = 24
A3 = Average time = A1/A2 = 5 minutes = (2/24)/24 = 2/576 of a day = 0.00347222…
If you format cell A3 appropriately, it could display as 0:05 for example – see reply above.
>Next, I need to add the mintues to a time(i.e. 6:30 AM
>+ 5 minutes (from calculation above) = 6:35 AM).
>Finally I need to add more minutes (time it takes for
>someone to push a cart from kitchen to hospital unit)
>to the time we just got (i.e. 6:35 AM + 5 Minutes = 6:40 AM).
Okay, 6:30am will always be 6.5 hours past midnight = N+(6.5/24) where N is any whole number representing the date that you want. If N=0, then Excel will assume you mean 0 Jan 1900 (!), however, perhaps you never actually care about the date?
So:
A5 = (6.5/24) = 0.2708333…. Representing 6:30am (you can format as you prefer – see above)
A3 = 5 Mins = (5/60)/24 = 0.00347222…. (from above)
A6 = A5+A3 = (6/24)+(((30+5)/60)/24) = 0.27430555… = 6:35 hours past midnight
Hope that all makes sense!
Alan.”

How do I write a formula to calculate the difference of 2 cells containing times, but only if the difference is greater than 1 min. example: a1 is 12:00:00 and a2 is 12:01:35 I want b1 to show the difference of 0:01:35, but if a2 is only 12:00:27 I dont want b1 to show the anything

“Hi Chris,
If you have those times in cells A1 and B1, then the following formula will do it:
=IF(B1-A1<((1/24)/60),"""",B1-A1)
I have assumed that the times are entered using Excel's date / time values, not as, for example, text.
Alan."

I am having problems figuring out how to set my timesheet up. The times are note figuring out correctly and the formulas are note working. HELP I Day In Out In Out In Out Regular Hours Overtime Hours Make Up Hours Holiday Hours Vacation Sick Other Hours Type Total Hours at Other Rate

“A1 and A2 are times. Ex 12:00:00 and 12:10:00
So I want C2 to equal 3 different things:
If A1 =A2 I want C3 to say On Time
If A1
If A1>A2 I want C3 to say Early
I tried several formulas but it just returns #VALUE!
I thought the correct formula should be:
=IF(A1=A2,”On Time”)+IF(A1>A2,”Early”)+IF(A1
Please Help “

The spreadsheet I am using has time listed as 07:00. I want to be able to ten key in the times. Is there a lookup formula that could change the 07:00 to 0700? If so how do I get the one cell to reference the chart?

“Hi John,
I don’t understand what you want to achieve.
Please could you elaborate a little.
Specifically, you ask if there is some way to change 07:00 to 0700, but at the same time you talk about faster inputting. I would suggest that inputting 0700 is actually faster than typing the extra character to get 07:00, so you would actually be better of just going direct to input 0700.
You also mention a chart. What are you charting?
Thanks,
Alan.”

I flew in the RAF for 27 years and have recently tried to audit my logbook additions. The times are in Hrs and 5min increments. It is cumbersome so I was interested in this tip. However, the wrong spreadsheet appears to have been added to this tip. It is the same as for the previous tip and does not relate to the text describing the tip. e.g. cells E4:E8 is hh:mm. Do you have a way of adding columns of hrs and minutes?

I need to work out difference between 2 dates and times in excel, but in working hours only, ignoring off duty hours (Weekends are working days). i.e working hours are 0800 to 2000 – start date/time 21/04/03 09:00 end date/time 22/04/03 17:00 – but for some rows the actual start time is prior to 08:00 and on others the end time is after 20:00 so the formulas I have tried so far havent worked properly – can anyone help?

Is there an easy way to convert hours to read as standard time rather than military time? I am automating time sheets for my emplyees and I am finding it difficult to show 4:00 PM rather than 16:00 PM. -anyone?

“Here is my decision:
1. write in column A (for example) hours listed (i.e. 1:00, 1:15, :45, etc.)
2. in cell B1 (or any other you want) write this formula: =VALUE(SUBSTITUTE(TEXT(A1;””HH.MM””);LEFT(TEXT(A1;””HH.MM””);3);””””))+VALUE(LEFT(TEXT(A1;””HH.MM””);2))*60
3. Copy & Paste the formula as much as it is necessary
If you have any problems or questions – write to my e-mail: koliu_m@yahoo.com or ICQ: 56468899″

im trying to write a formula that calculate the difference between the start and finish of my staff as well as taking the break time off the total … eg 9 hours work = 8 pay, 6 work 5.5 pay … any ideas ??? THANK YOU

“I have a spreadsheet that I’d like to show times that include minutes as 1/4 of an hour, or 1/2 of an hour. EG – 5:15 shows as 5.25 hours; 5:30 shows as 5.5 hours; 5:45 shows as 5.75 hours
Thank you very much for your help.”

“Lori,
You have to explain to Excel that one day has 24 hours. So, first you have to format the cell as [h]:mm. It will show 5:15 in cell A1, for instance. Then, in cell A2, you multiply A1 for 24, and you will have 5,25 hours. Note that the format in cell A2 is general number.
This tip can also be very helpful for those who need to calculate timesheets, employee payments, etc.”

“I am working on a spread sheet that I need to calculate an average time it takes to do something (in minutes) (i.e. Number of Trays Total / Average Trays Per Minute = Minutes it Takes to Fill Trays).
Next, I need to add the mintues to a time(i.e. 6:30 AM + 5 minutes (from calculation above) = 6:35 AM).
Finally I need to add more minutes (time it takes for someone to push a cart from kitchen to hospital unit) to the time we just got (i.e. 6:35 AM + 5 Minutes = 6:40 AM).

I am working using functions IF statements and I need to be able to calculate the duration between different times. Day Rate = 08:00 – 18:00, EVE Rate = 18:00 – 08:00 and Wkend Rate = Midnight Fri to Midnight Sun. So for a call starting on Thursday 17:00 and ending on Friday 19:00 would be charged for 1hr at day rate and 1hr at evening rate. How can i display this in Excel. I can break it down into many cells as poss if need be. I am finding this impossible, any experts out there

I flew in the RAF for 27 years and have recently tried to audit my logbook additions. The times are in Hrs and 5min increments. It is cumbersome so I was interested in this tip. However, the wrong spreadsheet appears to have been added to this tip. It is the same as for the previous tip and does not relate to the text describing the tip. e.g. cells E4:E8 is hh:mm. Do you have a way of adding columns of hrs and minutes?

I need to work out difference between 2 dates and times in excel, but in working hours only, ignoring off duty hours (Weekends are working days). i.e working hours are 0800 to 2000 – start date/time 21/04/03 09:00 end date/time 22/04/03 17:00 – but for some rows the actual start time is prior to 08:00 and on others the end time is after 20:00 so the formulas I have tried so far havent worked properly – can anyone help?

Is there an easy way to convert hours to read as standard time rather than military time? I am automating time sheets for my emplyees and I am finding it difficult to show 4:00 PM rather than 16:00 PM. -anyone?

“Here is my decision:
1. write in column A (for example) hours listed (i.e. 1:00, 1:15, :45, etc.)
2. in cell B1 (or any other you want) write this formula: =VALUE(SUBSTITUTE(TEXT(A1;””HH.MM””);LEFT(TEXT(A1;””HH.MM””);3);””””))+VALUE(LEFT(TEXT(A1;””HH.MM””);2))*60
3. Copy & Paste the formula as much as it is necessary
If you have any problems or questions – write to my e-mail: koliu_m@yahoo.com or ICQ: 56468899″

im trying to write a formula that calculate the difference between the start and finish of my staff as well as taking the break time off the total … eg 9 hours work = 8 pay, 6 work 5.5 pay … any ideas ??? THANK YOU

“I have a spreadsheet that I’d like to show times that include minutes as 1/4 of an hour, or 1/2 of an hour. EG – 5:15 shows as 5.25 hours; 5:30 shows as 5.5 hours; 5:45 shows as 5.75 hours
Thank you very much for your help.”

“Lori,
You have to explain to Excel that one day has 24 hours. So, first you have to format the cell as [h]:mm. It will show 5:15 in cell A1, for instance. Then, in cell A2, you multiply A1 for 24, and you will have 5,25 hours. Note that the format in cell A2 is general number.
This tip can also be very helpful for those who need to calculate timesheets, employee payments, etc.”

“I am working on a spread sheet that I need to calculate an average time it takes to do something (in minutes) (i.e. Number of Trays Total / Average Trays Per Minute = Minutes it Takes to Fill Trays).
Next, I need to add the mintues to a time(i.e. 6:30 AM + 5 minutes (from calculation above) = 6:35 AM).
Finally I need to add more minutes (time it takes for someone to push a cart from kitchen to hospital unit) to the time we just got (i.e. 6:35 AM + 5 Minutes = 6:40 AM).

I am working using functions IF statements and I need to be able to calculate the duration between different times. Day Rate = 08:00 – 18:00, EVE Rate = 18:00 – 08:00 and Wkend Rate = Midnight Fri to Midnight Sun. So for a call starting on Thursday 17:00 and ending on Friday 19:00 would be charged for 1hr at day rate and 1hr at evening rate. How can i display this in Excel. I can break it down into many cells as poss if need be. I am finding this impossible, any experts out there

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Privacy Overview

This website uses cookies so that we can provide you with the best user experience possible. Cookie information is stored in your browser and performs functions such as recognising you when you return to our website and helping our team to understand which sections of the website you find most interesting and useful.

You can adjust all of your cookie settings by navigating the tabs on the left hand side.

Strictly Necessary Cookies

Strictly Necessary Cookie should be enabled at all times so that we can save your preferences for cookie settings.

disable

If you disable this cookie, we will not be able to save your preferences. This means that every time you visit this website you will need to enable or disable cookies again.