| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding date/time in format dd/mm/yyyy hh/mm
I am struggling to subtract a start date/time cell from a finish date/time
cell. I can do it using the =sum function, but this gives the total hour difference. I need to change this to working hours (those hours between 9&5), any ideas?? Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding date/time in format dd/mm/yyyy hh/mm
http://www.cpearson.com/excel/DateTimeWS.htm
-- Regards, Peo Sjoblom "DaveAsh" wrote in message ... I am struggling to subtract a start date/time cell from a finish date/time cell. I can do it using the =sum function, but this gives the total hour difference. I need to change this to working hours (those hours between 9&5), any ideas?? Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding date/time in format dd/mm/yyyy hh/mm
Hi
=MIN(TIME(17,0,0),Finish Time)-MAX(TIME(9,0,0),Start Time) -- Regards Roger Govier "DaveAsh" wrote in message ... I am struggling to subtract a start date/time cell from a finish date/time cell. I can do it using the =sum function, but this gives the total hour difference. I need to change this to working hours (those hours between 9&5), any ideas?? Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding date/time in format dd/mm/yyyy hh/mm
=sum does not subtract; it adds.
-- David Biddulph "DaveAsh" wrote in message ... I am struggling to subtract a start date/time cell from a finish date/time cell. I can do it using the =sum function, but this gives the total hour difference. I need to change this to working hours (those hours between 9&5), any ideas?? Thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding date/time in format dd/mm/yyyy hh/mm
Using this formula gives the error #NAME? when a difference of more than 1
day is found, why is this? "Peo Sjoblom" wrote: http://www.cpearson.com/excel/DateTimeWS.htm -- Regards, Peo Sjoblom "DaveAsh" wrote in message ... I am struggling to subtract a start date/time cell from a finish date/time cell. I can do it using the =sum function, but this gives the total hour difference. I need to change this to working hours (those hours between 9&5), any ideas?? Thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding date/time in format dd/mm/yyyy hh/mm
Hi Dave
As Chip says on his page Since these formulas use the NETWORKDAYS worksheet function, you must the Analysis Tool Pack installed in order to use the formula. ToolsAddinscheck Analysis Toolpak -- Regards Roger Govier "DaveAsh" wrote in message ... Using this formula gives the error #NAME? when a difference of more than 1 day is found, why is this? "Peo Sjoblom" wrote: http://www.cpearson.com/excel/DateTimeWS.htm -- Regards, Peo Sjoblom "DaveAsh" wrote in message ... I am struggling to subtract a start date/time cell from a finish date/time cell. I can do it using the =sum function, but this gives the total hour difference. I need to change this to working hours (those hours between 9&5), any ideas?? Thanks |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding date/time in format dd/mm/yyyy hh/mm
The analysis toolpack is checked and the formula still does not work for more
than one day. "Roger Govier" wrote: Hi Dave As Chip says on his page Since these formulas use the NETWORKDAYS worksheet function, you must the Analysis Tool Pack installed in order to use the formula. ToolsAddinscheck Analysis Toolpak -- Regards Roger Govier "DaveAsh" wrote in message ... Using this formula gives the error #NAME? when a difference of more than 1 day is found, why is this? "Peo Sjoblom" wrote: http://www.cpearson.com/excel/DateTimeWS.htm -- Regards, Peo Sjoblom "DaveAsh" wrote in message ... I am struggling to subtract a start date/time cell from a finish date/time cell. I can do it using the =sum function, but this gives the total hour difference. I need to change this to working hours (those hours between 9&5), any ideas?? Thanks |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding date/time in format dd/mm/yyyy hh/mm
What does the page referred to say about a specific function the formula
uses, and what needs to be installed and enabled to use that function? -- David Biddulph "DaveAsh" wrote in message ... Using this formula gives the error #NAME? when a difference of more than 1 day is found, why is this? "Peo Sjoblom" wrote: http://www.cpearson.com/excel/DateTimeWS.htm "DaveAsh" wrote in message ... I am struggling to subtract a start date/time cell from a finish date/time cell. I can do it using the =sum function, but this gives the total hour difference. I need to change this to working hours (those hours between 9&5), any ideas?? Thanks |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding date/time in format dd/mm/yyyy hh/mm
#NAME? means that there is a text string in the formula that Excel cannot
understand as a name - maybe it's not defined. So which part of the formula is giving this trouble? Select the cell with the formula. In the formula bar (just above the top of the sheet), you can select any part of the formula (that would be a valid, complete formula on its own) and press F9 to display the result. One part or another will be returning #NAME? Don't forget to press ESC to get out of this mode each time you have finished looking at a partial formula result. This will enable you to home in on the problem 'name'. "DaveAsh" wrote in message ... The analysis toolpack is checked and the formula still does not work for more than one day. "Roger Govier" wrote: Hi Dave As Chip says on his page Since these formulas use the NETWORKDAYS worksheet function, you must the Analysis Tool Pack installed in order to use the formula. ToolsAddinscheck Analysis Toolpak -- Regards Roger Govier "DaveAsh" wrote in message ... Using this formula gives the error #NAME? when a difference of more than 1 day is found, why is this? "Peo Sjoblom" wrote: http://www.cpearson.com/excel/DateTimeWS.htm -- Regards, Peo Sjoblom "DaveAsh" wrote in message ... I am struggling to subtract a start date/time cell from a finish date/time cell. I can do it using the =sum function, but this gives the total hour difference. I need to change this to working hours (those hours between 9&5), any ideas?? Thanks |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding date/time in format dd/mm/yyyy hh/mm
Excel occasionally fails to enable the ToolPak correctly. It might be worth
trying to close Excel and reopen, or disable and reenable the ToolPak, or even reboot. The other question is whether you are sure that you've correectly named the various variables which the formula is using, as per the table. If all of that doesn't give you an explanation, I would recommend breaking the formula down into manageable chunks to see which part is giving the error. I would try the NETWORKDAYS part first. -- David Biddulph "DaveAsh" wrote in message ... The analysis toolpack is checked and the formula still does not work for more than one day. "Roger Govier" wrote: Hi Dave As Chip says on his page Since these formulas use the NETWORKDAYS worksheet function, you must the Analysis Tool Pack installed in order to use the formula. ToolsAddinscheck Analysis Toolpak -- Regards Roger Govier "DaveAsh" wrote in message ... Using this formula gives the error #NAME? when a difference of more than 1 day is found, why is this? "Peo Sjoblom" wrote: http://www.cpearson.com/excel/DateTimeWS.htm -- Regards, Peo Sjoblom "DaveAsh" wrote in message ... I am struggling to subtract a start date/time cell from a finish date/time cell. I can do it using the =sum function, but this gives the total hour difference. I need to change this to working hours (those hours between 9&5), any ideas?? Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Format date dd.mm.yyyy to dd/mm/yyyy | Excel Discussion (Misc queries) | |||
| change date format from dd/mm/yyyy to mm/yyyy | Excel Discussion (Misc queries) | |||
| how do I change date from mm/dd/yyyy to dd:mm:yyyy format in Excel | New Users to Excel | |||
| Set Permanent Date Format to MM//DD/YYYY | Excel Discussion (Misc queries) | |||
| How do I convert a date&time (yyyy-mm-dd hh:mm:ss) to date only? | Excel Worksheet Functions | |||
Linear Mode
