Hi
I am trying to calculate the difference between 2 dates including time.
I have modified the networkdays formula to get the results but still it shows incorrect date and time.
Example:=NETWORKDAYS(A2,B2,RDATA!$AC$2:$AC$71)-IF(MOD(A2,1)<=MOD(B2,1),0,1)&"\"&TEXT(B2-A2,"hh")&"\"&RIGHT(TEXT(B2-A2,"hh:mm"),2)
Start Date: 22/06/2015 22:19
End Date:22/06/2015 22:49
Result I Got: 1\00\30
But result should be 0\00\30
Regards
Rem0
Hi
Can you anyone advise on the query?
Best
Rem0
HI can any one help on this, still going in circles.
Digged in further and the below formula works fine for dates within a month and its not working as expected if the dates between 2 months.
Any help/ guidance is much awaited.=NETWORKDAYS(A2,B2,RDATA!$AC$2:$AC$71)-1&"\"&TEXT(B2-A2,"hh")&"\"&RIGHT(TEXT(B2-A2,"hh:mm"),2)
Best
Rem0
Digged in further and the below formula works fine for dates within a month and its not working as expected if the dates between 2 months.
Any help/ guidance is much awaited.=NETWORKDAYS(A2,B2,RDATA!$AC$2:$AC$71)-1&"\"&TEXT(B2-A2,"hh")&"\"&RIGHT(TEXT(B2-A2,"hh:mm"),2)
Best
Rem0
Hi
Any advice / guidance?
Still stuck on this...
check this for calculating working hours:
http://chandoo.org/wp/2010/09/10/working-hours-formula/
you can download the workbook at the bottom of the page...
PLS DO NOT PM; OPEN A THREAD INSTEAD!!!
1) Posting Code
[CODE]PasteYourCodeHere[/CODE]
(or paste your code, select it, click # button)
2) Uploading File(s)
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.
3) Testing the Codes
always back up your files before testing the codes.
4) Marking the Thread as Solved
from Thread Tools (on the top right corner, above the first message)
mancubus...Thanks
I managed to custom the formula for my need, but still facing an issue with that.
If you can take a look on the below link and advise that will be great.
http://chandoo.org/wp/2010/09/10/wor...omment-1005705
what's that?
i see nothing new there...
PLS DO NOT PM; OPEN A THREAD INSTEAD!!!
1) Posting Code
[CODE]PasteYourCodeHere[/CODE]
(or paste your code, select it, click # button)
2) Uploading File(s)
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.
3) Testing the Codes
always back up your files before testing the codes.
4) Marking the Thread as Solved
from Thread Tools (on the top right corner, above the first message)
may be its still under moderation.
here it is...
I have managed to custom the formula and it works fine but it throws error for below start and end date (all GMT).
Start: 21/06/2015 17:49
End: 22/06/2015 06:55
Not sure what the error is but will be helpful if you can point out anything in the formula.=TEXT((24*(NETWORKDAYS(A4,B4,RDATA!$AC$2:$AC$71)-1)-24*((MAX(MOD(A4,1),MOD(B4,1))-MIN(MOD(A4,1),MOD(B4,1)))))/24,”dd/hh/mm”)
i am not sure dd/hh/mm is a valid date/time format.
second issue is, especially when you copy a formula from a web site, make sure special characters (such as single or double quotes) are valid excel characters...
PLS DO NOT PM; OPEN A THREAD INSTEAD!!!
1) Posting Code
[CODE]PasteYourCodeHere[/CODE]
(or paste your code, select it, click # button)
2) Uploading File(s)
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.
3) Testing the Codes
always back up your files before testing the codes.
4) Marking the Thread as Solved
from Thread Tools (on the top right corner, above the first message)
Apologies!
here you go...
for the dd/hh/mm have tested and works fine...=TEXT((24*(NETWORKDAYS(A5,B5,RDATA!$AC$2:$AC$71)-1)-24*((MAX(MOD(A5,1),MOD(B5,1))-MIN(MOD(A5,1),MOD(B5,1)))))/24,"dd/hh/mm")
if you can advise why the above formula fails for the date range given above will be great help...
see if this helps...
http://answers.microsoft.com/en-us/o...deba755?auth=1
as you can see i am just googling...
PLS DO NOT PM; OPEN A THREAD INSTEAD!!!
1) Posting Code
[CODE]PasteYourCodeHere[/CODE]
(or paste your code, select it, click # button)
2) Uploading File(s)
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.
3) Testing the Codes
always back up your files before testing the codes.
4) Marking the Thread as Solved
from Thread Tools (on the top right corner, above the first message)
mancubus...
managed to break thru...
May be it might help others....
Best=IF(AND(DAY(A2)<>DAY(B2),MONTH(A2)<>MONTH(B2),OR(MOD(A2,1)>MOD(B2,1),MOD(A2,1)<MOD(B2,1))),TEXT((24*(NETWORKDAYS(A2,B2,RDATA!$AC$2:$AC$71))-24*((MOD(A2,1)-MOD(B2,1))))/24-1,"d/hh/mm"),IF(AND(DAY(A2)<>DAY(B2),MONTH(A2)=MONTH(B2),MOD(A2,1)<MOD(B2,1)),TEXT((24*(NETWORKDAYS(A2,B2,RDATA!$AC$2:$AC$71))-24*((MOD(A2,1)-MOD(B2,1))))/24-1,"d/hh/mm"),IF(AND(DAY(A2)<>DAY(B2),MONTH(A2)=MONTH(B2),MOD(A2,1)>=MOD(B2,1),MAX(A2:B2)-MIN(A2:B2)>=6),NETWORKDAYS(A2,B2,RDATA!$AC$2:$AC$71)-IF(MOD(A2,1)<=MOD(B2,1),0,1)-1&"/"&TEXT(B2-A2,"hh")&"/"&RIGHT(TEXT(B2-A2,"hh:mm"),2),IF(AND(DAY(A2)<>DAY(B2),MONTH(A2)=MONTH(B2),MOD(A2,1)>MOD(B2,1),MAX(A2:B2)-MIN(A2:B2)<6),NETWORKDAYS(A2,B2,RDATA!$AC$2:$AC$71)-IF(MOD(A2,1)<=MOD(B2,1),0,1)&"/"&TEXT(B2-A2,"hh")&"/"&RIGHT(TEXT(B2-A2,"hh:mm"),2),IF(AND(DAY(A2)=DAY(B2),MONTH(A2)=MONTH(B2),MOD(A2,1)<MOD(B2,1)),NETWORKDAYS(A2,B2,RDATA!$AC$2:$AC$71)-IF(MOD(A2,1)<=MOD(B2,1),0,1)-1&"/"&TEXT(B2-A2,"hh")&"/"&RIGHT(TEXT(B2-A2,"hh:mm"),2),"")))))
Rem0
Note: The result shows as No. of Days / No. of hours / No. of Minutes....
dd / hh / mm
Thanks for posting the solution. This time I will use the Thread Tool menu to mark the thread Solved for you.
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
Posting Permissions
Reply With Quote
