Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to project the due date according to a number of working hours
Does anyone know how to project the due date according to a required number
of working hours? such as If today is 24 Oct, 2005 (Monday) at 9:00am, my project required 100 hours to be finished, then how to create a formula and to project the due date in excel sheet? Working hour: 9am - 1pm and 2pm - 5pm (total 8 working hour per day) Thank for any suggestion Eric |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to project the due date according to a number of working hours
Assume starting date/time (e.g. now) is in cell A1, number of hours to work
is in A2: Completion date formula: =WORKDAY(A1,INT(A2/8)) Completion time (if you want it): =A1-INT(A1)+(A2-8*INT(A2/8))/24 explanation: A1-INT(A1) gives the time (decimal) portion of a date/time value. INT(A2/8) gives the integer number of days in the work hours A2 - (integer days) gives the number of extra hours to work Divide that by 24 to turn it into a decimal amount equivalent to 1 hour in Excel's equivalent date/time value (1 = 1 day, so hours/24 = fractional day) -- - K Dales "Eric" wrote: Does anyone know how to project the due date according to a required number of working hours? such as If today is 24 Oct, 2005 (Monday) at 9:00am, my project required 100 hours to be finished, then how to create a formula and to project the due date in excel sheet? Working hour: 9am - 1pm and 2pm - 5pm (total 8 working hour per day) Thank for any suggestion Eric |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to project the due date according to a number of working h
I missed one part of the problem:
If the finish time is after 1 pm, we need to add an hour (to account for the lunch hour in the working day) =IF(A1-INT(A1)+(A2-8*INT(A2/8))/2413/24,A1-INT(A1)+(A2-8*INT(A2/8))/24+1,A1-INT(A1)+(A2-8*INT(A2/8))/24) -- - K Dales "K Dales" wrote: Assume starting date/time (e.g. now) is in cell A1, number of hours to work is in A2: Completion date formula: =WORKDAY(A1,INT(A2/8)) Completion time (if you want it): =A1-INT(A1)+(A2-8*INT(A2/8))/24 explanation: A1-INT(A1) gives the time (decimal) portion of a date/time value. INT(A2/8) gives the integer number of days in the work hours A2 - (integer days) gives the number of extra hours to work Divide that by 24 to turn it into a decimal amount equivalent to 1 hour in Excel's equivalent date/time value (1 = 1 day, so hours/24 = fractional day) -- - K Dales "Eric" wrote: Does anyone know how to project the due date according to a required number of working hours? such as If today is 24 Oct, 2005 (Monday) at 9:00am, my project required 100 hours to be finished, then how to create a formula and to project the due date in excel sheet? Working hour: 9am - 1pm and 2pm - 5pm (total 8 working hour per day) Thank for any suggestion Eric |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to project the due date according to a number of working h
One more time (yikes!) - in my haste I forgot to divide the extra hour by 24
=IF(A1-INT(A1)+(A2-8*INT(A2/8))/2413/24,A1-INT(A1)+(A2-8*INT(A2/8))/24+1/24,A1-INT(A1)+(A2-8*INT(A2/8))/24) -- - K Dales "K Dales" wrote: Assume starting date/time (e.g. now) is in cell A1, number of hours to work is in A2: Completion date formula: =WORKDAY(A1,INT(A2/8)) Completion time (if you want it): =A1-INT(A1)+(A2-8*INT(A2/8))/24 explanation: A1-INT(A1) gives the time (decimal) portion of a date/time value. INT(A2/8) gives the integer number of days in the work hours A2 - (integer days) gives the number of extra hours to work Divide that by 24 to turn it into a decimal amount equivalent to 1 hour in Excel's equivalent date/time value (1 = 1 day, so hours/24 = fractional day) -- - K Dales "Eric" wrote: Does anyone know how to project the due date according to a required number of working hours? such as If today is 24 Oct, 2005 (Monday) at 9:00am, my project required 100 hours to be finished, then how to create a formula and to project the due date in excel sheet? Working hour: 9am - 1pm and 2pm - 5pm (total 8 working hour per day) Thank for any suggestion Eric |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to project the due date according to a number of working h
Thank K Dales
A1 = 2005/1/3 11:00 AM A2 = 100 According to following code =IF(A1-INT(A1)+(A2-8*INT(A2/8))/2413/24,A1-INT(A1)+(A2-8*INT(A2/8))/24+1/24,A1-INT(A1)+(A2-8*INT(A2/8))/24) The result is shown below =1900/1/0 4:00 PM There is something wrong with the date, do you know how to fix it? Furthermore, I don't know what wrong it is, once I type following code =WORKDAY(A1,INT(A2/8)), then it displays #NAME? Do you have any idea how to fix it too? Thank you very much Eric "K Dales" wrote: One more time (yikes!) - in my haste I forgot to divide the extra hour by 24 =IF(A1-INT(A1)+(A2-8*INT(A2/8))/2413/24,A1-INT(A1)+(A2-8*INT(A2/8))/24+1/24,A1-INT(A1)+(A2-8*INT(A2/8))/24) -- - K Dales "K Dales" wrote: Assume starting date/time (e.g. now) is in cell A1, number of hours to work is in A2: Completion date formula: =WORKDAY(A1,INT(A2/8)) Completion time (if you want it): =A1-INT(A1)+(A2-8*INT(A2/8))/24 explanation: A1-INT(A1) gives the time (decimal) portion of a date/time value. INT(A2/8) gives the integer number of days in the work hours A2 - (integer days) gives the number of extra hours to work Divide that by 24 to turn it into a decimal amount equivalent to 1 hour in Excel's equivalent date/time value (1 = 1 day, so hours/24 = fractional day) -- - K Dales "Eric" wrote: Does anyone know how to project the due date according to a required number of working hours? such as If today is 24 Oct, 2005 (Monday) at 9:00am, my project required 100 hours to be finished, then how to create a formula and to project the due date in excel sheet? Working hour: 9am - 1pm and 2pm - 5pm (total 8 working hour per day) Thank for any suggestion Eric |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to project the due date according to a number of working h
Eric: sorry it took a while, just noticed this post again
For the first part (completion time formula); it may seem strange but the result is as I intended. I was only trying to calculate the time portion, and my intention (though I apologize for not making this clear) was to format this in the cell as a time value. My "completion date formula" was in one cell to show the date due, and then I was showing time due in the next cell over. Adding the two formulas together should give the overall result. In Excel, all date/time values are actually numeric with the integer part being days and the fractional part being hours/minutes/seconds within the day. Day "zero" is January 0, 1900 to Excel. If you add the time to the date calculated it will give the whole thing in one cell value. As for the other, it appears to me that Excel is not recognizing the WORKDAY function. If you pull up the list of functions from the "Paste Function" button on the toolbar, does this show WORKDAY in the Date function group? Or if you look in the help file? I use Excel 2000; I am not sure if you use an earlier version how far back this function was available. -- - K Dales "Eric" wrote: Thank K Dales A1 = 2005/1/3 11:00 AM A2 = 100 According to following code =IF(A1-INT(A1)+(A2-8*INT(A2/8))/2413/24,A1-INT(A1)+(A2-8*INT(A2/8))/24+1/24,A1-INT(A1)+(A2-8*INT(A2/8))/24) The result is shown below =1900/1/0 4:00 PM There is something wrong with the date, do you know how to fix it? Furthermore, I don't know what wrong it is, once I type following code =WORKDAY(A1,INT(A2/8)), then it displays #NAME? Do you have any idea how to fix it too? Thank you very much Eric "K Dales" wrote: One more time (yikes!) - in my haste I forgot to divide the extra hour by 24 =IF(A1-INT(A1)+(A2-8*INT(A2/8))/2413/24,A1-INT(A1)+(A2-8*INT(A2/8))/24+1/24,A1-INT(A1)+(A2-8*INT(A2/8))/24) -- - K Dales "K Dales" wrote: Assume starting date/time (e.g. now) is in cell A1, number of hours to work is in A2: Completion date formula: =WORKDAY(A1,INT(A2/8)) Completion time (if you want it): =A1-INT(A1)+(A2-8*INT(A2/8))/24 explanation: A1-INT(A1) gives the time (decimal) portion of a date/time value. INT(A2/8) gives the integer number of days in the work hours A2 - (integer days) gives the number of extra hours to work Divide that by 24 to turn it into a decimal amount equivalent to 1 hour in Excel's equivalent date/time value (1 = 1 day, so hours/24 = fractional day) -- - K Dales "Eric" wrote: Does anyone know how to project the due date according to a required number of working hours? such as If today is 24 Oct, 2005 (Monday) at 9:00am, my project required 100 hours to be finished, then how to create a formula and to project the due date in excel sheet? Working hour: 9am - 1pm and 2pm - 5pm (total 8 working hour per day) Thank for any suggestion Eric |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to project the due date according to a number of working h
Thank K Dales for your reply
The problem was solved, Thank you Eric "K Dales" wrote: Eric: sorry it took a while, just noticed this post again For the first part (completion time formula); it may seem strange but the result is as I intended. I was only trying to calculate the time portion, and my intention (though I apologize for not making this clear) was to format this in the cell as a time value. My "completion date formula" was in one cell to show the date due, and then I was showing time due in the next cell over. Adding the two formulas together should give the overall result. In Excel, all date/time values are actually numeric with the integer part being days and the fractional part being hours/minutes/seconds within the day. Day "zero" is January 0, 1900 to Excel. If you add the time to the date calculated it will give the whole thing in one cell value. As for the other, it appears to me that Excel is not recognizing the WORKDAY function. If you pull up the list of functions from the "Paste Function" button on the toolbar, does this show WORKDAY in the Date function group? Or if you look in the help file? I use Excel 2000; I am not sure if you use an earlier version how far back this function was available. -- - K Dales "Eric" wrote: Thank K Dales A1 = 2005/1/3 11:00 AM A2 = 100 According to following code =IF(A1-INT(A1)+(A2-8*INT(A2/8))/2413/24,A1-INT(A1)+(A2-8*INT(A2/8))/24+1/24,A1-INT(A1)+(A2-8*INT(A2/8))/24) The result is shown below =1900/1/0 4:00 PM There is something wrong with the date, do you know how to fix it? Furthermore, I don't know what wrong it is, once I type following code =WORKDAY(A1,INT(A2/8)), then it displays #NAME? Do you have any idea how to fix it too? Thank you very much Eric "K Dales" wrote: One more time (yikes!) - in my haste I forgot to divide the extra hour by 24 =IF(A1-INT(A1)+(A2-8*INT(A2/8))/2413/24,A1-INT(A1)+(A2-8*INT(A2/8))/24+1/24,A1-INT(A1)+(A2-8*INT(A2/8))/24) -- - K Dales "K Dales" wrote: Assume starting date/time (e.g. now) is in cell A1, number of hours to work is in A2: Completion date formula: =WORKDAY(A1,INT(A2/8)) Completion time (if you want it): =A1-INT(A1)+(A2-8*INT(A2/8))/24 explanation: A1-INT(A1) gives the time (decimal) portion of a date/time value. INT(A2/8) gives the integer number of days in the work hours A2 - (integer days) gives the number of extra hours to work Divide that by 24 to turn it into a decimal amount equivalent to 1 hour in Excel's equivalent date/time value (1 = 1 day, so hours/24 = fractional day) -- - K Dales "Eric" wrote: Does anyone know how to project the due date according to a required number of working hours? such as If today is 24 Oct, 2005 (Monday) at 9:00am, my project required 100 hours to be finished, then how to create a formula and to project the due date in excel sheet? Working hour: 9am - 1pm and 2pm - 5pm (total 8 working hour per day) Thank for any suggestion Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting number of hours per project per team | Excel Discussion (Misc queries) | |||
Calculate number days + hours to complete a project | Excel Worksheet Functions | |||
number of hours between two date/times | Excel Discussion (Misc queries) | |||
How to project the due date according to a number of working hours | Excel Discussion (Misc queries) | |||
How to project the due date according to a number of working hours | Excel Worksheet Functions |