| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
calculate # of days from two different times
If A1=Date Reported, B2=Date Resolved, B3=Resolution Period
What formular should I use to: 1) calculate the resolution period in days, excluding weekends and public holidays. 2) display "pending" in the Resolution Period cell if the Date Resolved cell does not have a time value 3) display "impossible" in the Resolution Period cell if the value in the Date Resolved Cell is less than the value in the Date reported cell 4) display "invalid" in the Resolution Period cell if either the Date Resolved Cell or the Date Reported cell has no values. Please assist... -- Fia Fiji Islands |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
calculate # of days from two different times
Hi Fia,
I think this should do what you want. (watch out for unwanted spaces if this formula wraps) =IF(INT(B2)-B2=0,"pending",IF(B2<A1,"Impossible",IF(OR(A1="",B 2=""),"Invalid",NETWORKDAYS(A1,B2)))) You will need to have the Analysis Tool Pack loaded otherwise NETWORKDAYS will return a #NAME error. Also look in the help file for how to add the public holidays to the NETWORKDAYS function. HTH Martin "Fia" wrote in message ... If A1=Date Reported, B2=Date Resolved, B3=Resolution Period What formular should I use to: 1) calculate the resolution period in days, excluding weekends and public holidays. 2) display "pending" in the Resolution Period cell if the Date Resolved cell does not have a time value 3) display "impossible" in the Resolution Period cell if the value in the Date Resolved Cell is less than the value in the Date reported cell 4) display "invalid" in the Resolution Period cell if either the Date Resolved Cell or the Date Reported cell has no values. Please assist... -- Fia Fiji Islands |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
calculate # of days from two different times
Woops,
I got the order of the error checking back to front. This might work better. =IF(OR(A1="",B2=""),"Invalid",IF(B2<A1,"Impossible ",IF(INT(B2)-B2=0,"pending",NETWORKDAYS(A1,B2)))) HTH Martin "MartinW" wrote in message ... Hi Fia, I think this should do what you want. (watch out for unwanted spaces if this formula wraps) =IF(INT(B2)-B2=0,"pending",IF(B2<A1,"Impossible",IF(OR(A1="",B 2=""),"Invalid",NETWORKDAYS(A1,B2)))) You will need to have the Analysis Tool Pack loaded otherwise NETWORKDAYS will return a #NAME error. Also look in the help file for how to add the public holidays to the NETWORKDAYS function. HTH Martin "Fia" wrote in message ... If A1=Date Reported, B2=Date Resolved, B3=Resolution Period What formular should I use to: 1) calculate the resolution period in days, excluding weekends and public holidays. 2) display "pending" in the Resolution Period cell if the Date Resolved cell does not have a time value 3) display "impossible" in the Resolution Period cell if the value in the Date Resolved Cell is less than the value in the Date reported cell 4) display "invalid" in the Resolution Period cell if either the Date Resolved Cell or the Date Reported cell has no values. Please assist... -- Fia Fiji Islands |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
calculate # of days from two different times
Hi Martin,
Thank you for replying. "Pending" should only be diplayed if the Date Resolved column is empty. Ideally, when users log an error report in the table, the Resolution Period column should display the following values for each scenario: DateReported DateResolved ResolutionPeriod 23/06/08 25/06/08 2 23/06/08 Pending 23/06/08 20/06/08 Impossible 20/06/08 Invalid Invalid Thanks, -- Fia Fiji Islands "MartinW" wrote: Woops, I got the order of the error checking back to front. This might work better. =IF(OR(A1="",B2=""),"Invalid",IF(B2<A1,"Impossible ",IF(INT(B2)-B2=0,"pending",NETWORKDAYS(A1,B2)))) HTH Martin "MartinW" wrote in message ... Hi Fia, I think this should do what you want. (watch out for unwanted spaces if this formula wraps) =IF(INT(B2)-B2=0,"pending",IF(B2<A1,"Impossible",IF(OR(A1="",B 2=""),"Invalid",NETWORKDAYS(A1,B2)))) You will need to have the Analysis Tool Pack loaded otherwise NETWORKDAYS will return a #NAME error. Also look in the help file for how to add the public holidays to the NETWORKDAYS function. HTH Martin "Fia" wrote in message ... If A1=Date Reported, B2=Date Resolved, B3=Resolution Period What formular should I use to: 1) calculate the resolution period in days, excluding weekends and public holidays. 2) display "pending" in the Resolution Period cell if the Date Resolved cell does not have a time value 3) display "impossible" in the Resolution Period cell if the value in the Date Resolved Cell is less than the value in the Date reported cell 4) display "invalid" in the Resolution Period cell if either the Date Resolved Cell or the Date Reported cell has no values. Please assist... -- Fia Fiji Islands |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
calculate # of days from two different times
OK, so in your first post, in your second requirement, where you said 'time
value' you meant 'date value' and in your fourth requirement where you said 'either' you meant neither. Is that right? If so then I think this should be what you are after. =IF(AND(A1="",B2=""),"Invalid",IF(B2="","pending", IF(B2<A1,"Impossible",NETWORKDAYS(A1,B2)))) NETWORKDAYS counts whole days and therefore it will count 23/06/08 to 25/06/08 as 3. To get it to return 2 you just have to subtract 1 from the result, so the formula will be. =IF(AND(A1="",B2=""),"Invalid",IF(B2="","pending", IF(B2<A1,"Impossible",NETWORKDAYS(A1,B2)-1))) Does that work any better? Regards Martin "Fia" wrote: Hi Martin, Thank you for replying. "Pending" should only be diplayed if the Date Resolved column is empty. Ideally, when users log an error report in the table, the Resolution Period column should display the following values for each scenario: DateReported DateResolved ResolutionPeriod 23/06/08 25/06/08 2 23/06/08 Pending 23/06/08 20/06/08 Impossible 20/06/08 Invalid Invalid Thanks, -- Fia Fiji Islands "MartinW" wrote: Woops, I got the order of the error checking back to front. This might work better. =IF(OR(A1="",B2=""),"Invalid",IF(B2<A1,"Impossible ",IF(INT(B2)-B2=0,"pending",NETWORKDAYS(A1,B2)))) HTH Martin "MartinW" wrote in message ... Hi Fia, I think this should do what you want. (watch out for unwanted spaces if this formula wraps) =IF(INT(B2)-B2=0,"pending",IF(B2<A1,"Impossible",IF(OR(A1="",B 2=""),"Invalid",NETWORKDAYS(A1,B2)))) You will need to have the Analysis Tool Pack loaded otherwise NETWORKDAYS will return a #NAME error. Also look in the help file for how to add the public holidays to the NETWORKDAYS function. HTH Martin "Fia" wrote in message ... If A1=Date Reported, B2=Date Resolved, B3=Resolution Period What formular should I use to: 1) calculate the resolution period in days, excluding weekends and public holidays. 2) display "pending" in the Resolution Period cell if the Date Resolved cell does not have a time value 3) display "impossible" in the Resolution Period cell if the value in the Date Resolved Cell is less than the value in the Date reported cell 4) display "invalid" in the Resolution Period cell if either the Date Resolved Cell or the Date Reported cell has no values. Please assist... -- Fia Fiji Islands |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
calculate # of days from two different times
OK, now I'm back on my own computer I can see your example formatted
properly. Try this. =IF(OR(A1="",AND(A1="",B2="")),"Invalid",IF(B2="", "pending",IF(B2<A1,"Impossible",NETWORKDAYS(A1 ,B2)-1))) HTH Martin "MartinW" wrote in message ... OK, so in your first post, in your second requirement, where you said 'time value' you meant 'date value' and in your fourth requirement where you said 'either' you meant neither. Is that right? If so then I think this should be what you are after. =IF(AND(A1="",B2=""),"Invalid",IF(B2="","pending", IF(B2<A1,"Impossible",NETWORKDAYS(A1,B2)))) NETWORKDAYS counts whole days and therefore it will count 23/06/08 to 25/06/08 as 3. To get it to return 2 you just have to subtract 1 from the result, so the formula will be. =IF(AND(A1="",B2=""),"Invalid",IF(B2="","pending", IF(B2<A1,"Impossible",NETWORKDAYS(A1,B2)-1))) Does that work any better? Regards Martin "Fia" wrote: Hi Martin, Thank you for replying. "Pending" should only be diplayed if the Date Resolved column is empty. Ideally, when users log an error report in the table, the Resolution Period column should display the following values for each scenario: DateReported DateResolved ResolutionPeriod 23/06/08 25/06/08 2 23/06/08 Pending 23/06/08 20/06/08 Impossible 20/06/08 Invalid Invalid Thanks, -- Fia Fiji Islands "MartinW" wrote: Woops, I got the order of the error checking back to front. This might work better. =IF(OR(A1="",B2=""),"Invalid",IF(B2<A1,"Impossible ",IF(INT(B2)-B2=0,"pending",NETWORKDAYS(A1,B2)))) HTH Martin "MartinW" wrote in message ... Hi Fia, I think this should do what you want. (watch out for unwanted spaces if this formula wraps) =IF(INT(B2)-B2=0,"pending",IF(B2<A1,"Impossible",IF(OR(A1="",B 2=""),"Invalid",NETWORKDAYS(A1,B2)))) You will need to have the Analysis Tool Pack loaded otherwise NETWORKDAYS will return a #NAME error. Also look in the help file for how to add the public holidays to the NETWORKDAYS function. HTH Martin "Fia" wrote in message ... If A1=Date Reported, B2=Date Resolved, B3=Resolution Period What formular should I use to: 1) calculate the resolution period in days, excluding weekends and public holidays. 2) display "pending" in the Resolution Period cell if the Date Resolved cell does not have a time value 3) display "impossible" in the Resolution Period cell if the value in the Date Resolved Cell is less than the value in the Date reported cell 4) display "invalid" in the Resolution Period cell if either the Date Resolved Cell or the Date Reported cell has no values. Please assist... -- Fia Fiji Islands |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Difference between dates/times in Days & Hours | Excel Discussion (Misc queries) | |||
| How do I calculate dates and times, w/answer of days & hours | Excel Worksheet Functions | |||
| Difference in two times over days | Excel Worksheet Functions | |||
| find days and hours between 2 sets of dates and times | Excel Discussion (Misc queries) | |||
| Need to calulate a sum for once a month X #days X #times used per. | Excel Worksheet Functions | |||
Linear Mode
