Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I calc the time difference in hh:mm for shift work?
Example of my data Col A1 = 16:00 (4PM) in time format Col B1 = 24:00 (midnight) Col C is formula TEXT(B1-A1,"h:mm") = 8:00 hrs which is correct. However, when I try to calculate from 24:00 (midnight) to 07:00 (7 AM )I get a #VALUE! error. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I calc the time difference in hh:mm for shift work?
B should be larger than A, to avoid negative times, which Excel doesn't
represent very well. One option is to use 0:00 instead of 24:00 Or include dates in the times. -- Kind regards, Niek Otten Microsoft MVP - Excel "TomS" wrote in message ... Example of my data Col A1 = 16:00 (4PM) in time format Col B1 = 24:00 (midnight) Col C is formula TEXT(B1-A1,"h:mm") = 8:00 hrs which is correct. However, when I try to calculate from 24:00 (midnight) to 07:00 (7 AM )I get a #VALUE! error. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I calc the time difference in hh:mm for shift work?
Why do you convert your result to text?
Use: =mod(b1-a1,1) and format the result as h:mm If you have to have the result as text, use: =text(mod(b1-a1,1),"h:mm") Regards, Fred. "TomS" wrote in message ... Example of my data Col A1 = 16:00 (4PM) in time format Col B1 = 24:00 (midnight) Col C is formula TEXT(B1-A1,"h:mm") = 8:00 hrs which is correct. However, when I try to calculate from 24:00 (midnight) to 07:00 (7 AM )I get a #VALUE! error. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I calc the time difference in hh:mm for shift work?
=IF((OR(H10="",G10="")),0,IF((H10<G10),((H10-G10)*24)+24,(H10-G10)*24)) G10 is start time, H10 is stop time. Change to your start and stop cell names. Sorry, but 24 hour format is required here. There may be a 12 hr format version I do not know about. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I calc the time difference in hh:mm for shift work?
Format of a cell is immaterial to its calculation. Formatting controls only
the display, not the way it's stored internally. Any formula that works with time, will work with all formats of it. In your response, you should indicate that you are converting time to decimal hours. The result would need to be formatted as a number. Regards, Fred. "Sum Ting Wong" <SumTingWong@thebarattheendoftheVenusianLightnigBo lt.org wrote in message ... =IF((OR(H10="",G10="")),0,IF((H10<G10),((H10-G10)*24)+24,(H10-G10)*24)) G10 is start time, H10 is stop time. Change to your start and stop cell names. Sorry, but 24 hour format is required here. There may be a 12 hr format version I do not know about. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I calc the time difference in hh:mm for shift work?
On Tue, 28 Apr 2009 08:03:27 -0600, "Fred Smith"
wrote: Format of a cell is immaterial to its calculation. Perhaps, but the code given is 24 hr explicit code, if you'll simply look at the numbers used to perform the calculations. Formatting controls only the display, not the way it's stored internally. It also becomes a data validator as it only allows data to be entered into such a cell in a matching format. Any formula that works with time, will work with all formats of it. OK In your response, you should indicate that you are converting time to decimal hours. The result would need to be formatted as a number. Yes, the answer is a decimal version, but anyone that tracks time segments in other than quarter hours is nuts to begin with, so I would also limit input into the time fields to quarter hour break points. Regards, Fred. "Sum Ting Wong" <SumTingWong@thebarattheendoftheVenusianLightnigBo lt.org wrote in message ... =IF((OR(H10="",G10="")),0,IF((H10<G10),((H10-G10)*24)+24,(H10-G10)*24)) G10 is start time, H10 is stop time. Change to your start and stop cell names. Sorry, but 24 hour format is required here. There may be a 12 hr format version I do not know about. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I calc the time difference in hh:mm for shift work?
Answers interspersed below.
-- David Biddulph "Archimedes' Lever" wrote in message ... On Tue, 28 Apr 2009 08:03:27 -0600, "Fred Smith" wrote: Format of a cell is immaterial to its calculation. Perhaps, but the code given is 24 hr explicit code, if you'll simply look at the numbers used to perform the calculations. Whether the time is displayed in 12 hour or 24 hour format will make no difference to the result of the calculation, as Fred said. Formatting controls only the display, not the way it's stored internally. It also becomes a data validator as it only allows data to be entered into such a cell in a matching format. Data validation and cell display formatting are two distinct features in Excel. You can't use cell display formatting to perform the data validation function. A cell displayed in 24 hour format doesn't prevent anyone entering time in 12 hour format, nor does it prevent time beyond 24 hours being entered. Any formula that works with time, will work with all formats of it. OK In your response, you should indicate that you are converting time to decimal hours. The result would need to be formatted as a number. Yes, the answer is a decimal version, but anyone that tracks time segments in other than quarter hours is nuts to begin with, so I would also limit input into the time fields to quarter hour break points. Regards, Fred. "Sum Ting Wong" <SumTingWong@thebarattheendoftheVenusianLightnigBo lt.org wrote in message ... =IF((OR(H10="",G10="")),0,IF((H10<G10),((H10-G10)*24)+24,(H10-G10)*24)) G10 is start time, H10 is stop time. Change to your start and stop cell names. Sorry, but 24 hour format is required here. There may be a 12 hr format version I do not know about. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I calc the time difference in hh:mm for shift work?
On Wed, 29 Apr 2009 13:01:38 +0100, "David Biddulph" <groups [at]
biddulph.org.uk wrote: Whether the time is displayed in 12 hour or 24 hour format will make no difference to the result of the calculation, as Fred said. As far as display of the figure goes, sure. But the formula will not work without the use of the number 24 simply because that is how large the set is. That forces the input to a set of 24. Everywhere I used it requires me to use 24 hr format for data input (maybe it is my validation) as using 11:00 for example and 1:00 as a stop time does not yield 2 hr. I have to use 13:00 for that to work right. So what am I missing? Unless the user types out, long form and adds the AM or PM designator, the data entry fails to calculate correctly in the tally cell. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I calc the time difference in hh:mm for shift work?
If you want to use 12 hour format, then of course you need to include the AM
or PM. That's what 12 hour format is. As I said in the part of my message which you snipped, data validation and cell display formatting are two separate unrelated features of Excel. Data validation can constrain the numbers that it will allow you to store in the cell. The format in which you choose to display the number is a separate matter. -- David Biddulph Archimedes' Lever wrote: On Wed, 29 Apr 2009 13:01:38 +0100, "David Biddulph" <groups [at] biddulph.org.uk wrote: Whether the time is displayed in 12 hour or 24 hour format will make no difference to the result of the calculation, as Fred said. As far as display of the figure goes, sure. But the formula will not work without the use of the number 24 simply because that is how large the set is. That forces the input to a set of 24. Everywhere I used it requires me to use 24 hr format for data input (maybe it is my validation) as using 11:00 for example and 1:00 as a stop time does not yield 2 hr. I have to use 13:00 for that to work right. So what am I missing? Unless the user types out, long form and adds the AM or PM designator, the data entry fails to calculate correctly in the tally cell. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I calc the time difference in hh:mm for shift work?
On Thu, 30 Apr 2009 09:13:55 +0100, "David Biddulph" <groups [at]
biddulph.org.uk wrote: If you want to use 12 hour format, then of course you need to include the AM or PM. That's what 12 hour format is. People entering start times or stop times typically do not want to futz around with any more than the least sized set of keypresses. So most would opt for hh:mm entry, which means that one MUST use 24 hr format for a sheet set up for quick entry. So you will see time tracking where 24 hr format is utilized more often than forcing a person to enter long format time declarations. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I calc the time difference in hh:mm for shift work?
On Thu, 30 Apr 2009 09:13:55 +0100, "David Biddulph" <groups [at]
biddulph.org.uk wrote: If you want to use 12 hour format, then of course you need to include the AM or PM. That's what 12 hour format is. As I said in the part of my message which you snipped, data validation and cell display formatting are two separate unrelated features of Excel. Data validation can constrain the numbers that it will allow you to store in the cell. The format in which you choose to display the number is a separate matter. The only exception, then, is if one chooses "custom" as the cell format, and sets that string to "hh:mm". Maybe it is a bug. ;-) That is why my cells would not accept any other input. After choosing one of the given "time" formats for cells, it works fine. Apparently my "custom" setup also forces a specific input mode, which ends up being a type of validation. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I calc the time difference in hh:mm for shift work?
You're not giving up, are you?
I agree people typically want to enter data with the minimum of keystrokes. I always enter times using 24-hour notation, regardless of how the cell is formatted. However, formatting a cell and entering data are two separate and distinct functions. You can easily enter a 24-hour time into a cell that's formatted for AM/PM. You could even do it yourself before you insist that it can't be done in your next reply. Regards, Fred. "Archimedes' Lever" wrote in message ... On Thu, 30 Apr 2009 09:13:55 +0100, "David Biddulph" <groups [at] biddulph.org.uk wrote: If you want to use 12 hour format, then of course you need to include the AM or PM. That's what 12 hour format is. People entering start times or stop times typically do not want to futz around with any more than the least sized set of keypresses. So most would opt for hh:mm entry, which means that one MUST use 24 hr format for a sheet set up for quick entry. So you will see time tracking where 24 hr format is utilized more often than forcing a person to enter long format time declarations. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I calc the time difference in hh:mm for shift work?
Yes, if it behaves that way for you, it's a bug. Perhaps you can tell us
which version of Excel you are using? For the rest of the world, we can custom format as hh:mm and still type in a number as 0.75 or 6:00 PM. It will still display as 18:00, but doesn't constrain the format in which we enter the number, nor does that format contrain the value which we can enter. -- David Biddulph Archimedes' Lever wrote: On Thu, 30 Apr 2009 09:13:55 +0100, "David Biddulph" <groups [at] biddulph.org.uk wrote: If you want to use 12 hour format, then of course you need to include the AM or PM. That's what 12 hour format is. As I said in the part of my message which you snipped, data validation and cell display formatting are two separate unrelated features of Excel. Data validation can constrain the numbers that it will allow you to store in the cell. The format in which you choose to display the number is a separate matter. The only exception, then, is if one chooses "custom" as the cell format, and sets that string to "hh:mm". Maybe it is a bug. ;-) That is why my cells would not accept any other input. After choosing one of the given "time" formats for cells, it works fine. Apparently my "custom" setup also forces a specific input mode, which ends up being a type of validation. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create formula to calc difference in dates? | Excel Discussion (Misc queries) | |||
Calc minutes and seconds difference | Excel Worksheet Functions | |||
Display minutes ONLY and calc difference | Excel Discussion (Misc queries) | |||
Can excel calc the difference between 2 dates removing weekends? | Excel Discussion (Misc queries) | |||
Percentage difference calc that knows the largest figure | Excel Discussion (Misc queries) |