| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ref: Formula to calculate elapsed time between certain dates and t
Is there a way that the function in the referenced post could include
weekends, i.e. 10/26/05 3:48 PM 11/1/05 9:07 AM? Function in posting: =SUM((18/24-MOD(A1,1))*(WEEKDAY(A1,2)<6),MOD(A15,1)-8/24* (WEEKDAY(A15,2)<6)+(NETWORKDAYS(A1,A15)-SUM(WEEKDAY(A1,2) <6,WEEKDAY(A15,2)<6))*10/24) This function totals the time as 5:19:00 when it actually should be 29:19:00. Thanks in advance for any help. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Ref: Formula to calculate elapsed time between certain datesand t
Hi
how have you got the cell with the formula formatted? It should be [h]:mm to allow Excel to go beyond 24 hours, rather than cycling back to zero again. With your dates and times and formula, I get a result of 33:19 Regards Roger Govier DrBarqs wrote: Is there a way that the function in the referenced post could include weekends, i.e. 10/26/05 3:48 PM 11/1/05 9:07 AM? Function in posting: =SUM((18/24-MOD(A1,1))*(WEEKDAY(A1,2)<6),MOD(A15,1)-8/24* (WEEKDAY(A15,2)<6)+(NETWORKDAYS(A1,A15)-SUM(WEEKDAY(A1,2) <6,WEEKDAY(A15,2)<6))*10/24) This function totals the time as 5:19:00 when it actually should be 29:19:00. Thanks in advance for any help. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Ref: Formula to calculate elapsed time between certain dates a
Hello.
Changing the time format worked! Thank you very much. "Roger Govier" wrote: Hi how have you got the cell with the formula formatted? It should be [h]:mm to allow Excel to go beyond 24 hours, rather than cycling back to zero again. With your dates and times and formula, I get a result of 33:19 Regards Roger Govier DrBarqs wrote: Is there a way that the function in the referenced post could include weekends, i.e. 10/26/05 3:48 PM 11/1/05 9:07 AM? Function in posting: =SUM((18/24-MOD(A1,1))*(WEEKDAY(A1,2)<6),MOD(A15,1)-8/24* (WEEKDAY(A15,2)<6)+(NETWORKDAYS(A1,A15)-SUM(WEEKDAY(A1,2) <6,WEEKDAY(A15,2)<6))*10/24) This function totals the time as 5:19:00 when it actually should be 29:19:00. Thanks in advance for any help. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula to calculate number of days between Dates | Excel Worksheet Functions | |||
| Can someone help with a formula to calculate dates? | Excel Discussion (Misc queries) | |||
| Formula to calculate elapsed time between certain dates and times | Excel Discussion (Misc queries) | |||
| formula to calculate # of days between dates, excluding holidays | Excel Discussion (Misc queries) | |||
| How to calculate time between two dates? | Setting up and Configuration of Excel | |||
Linear Mode
