• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Time subtract help

N

Nabeel

Member
Hello all

Plz find attached sample file ..here I want to calculate the difference of two working hour.in actual working hours is 234 and I want to do it like 234:00 (this is hours and minutes)but I am unable to do it after that want to subtract it to working hours..plz put the req and also guide

BR
 

Attachments

  • Sample File.xlsx
    16.7 KB · Views: 0
In E2: Try entering 234 as 234:00 or 234:00:00
Make sure Column F has a Custom Number Format of [h]:mm or [h]:mm:ss applied
 
Hi Nabeel,

Convert working and actual working hours as text so that there should not be space after hours like 225: 00(there is a space after :), convert it to '225:00(as text so that you don't need to put a space after :) and put formula in F2 cell: =TEXT(DOLLARDE(SUBSTITUTE(E2,":","."),60)/24-DOLLARDE(SUBSTITUTE(D2,":","."),60)/24,"[H]:MM")

It will work.

Thanks,

Krishna
 
hi there
i tried but its not working.plz see attached..real result is in G column...
BR
 

Attachments

  • Sample File.xlsx
    19.2 KB · Views: 0
Hi as I told in my previous reply, please make your hours as text also by using '(Apstrophy sign) as '225:00 and '234:00 then it will give correct result. Try noow.

You can do it all by formula like(="'"&D2 ) and (="'"&E2)

Thanks
 
hi
thnx for helping
plz put these changes in sheet and upload it will be easy to me for understanding.
thnx
Regards
 
Hi Nabeel,

Not sure about the result, please check this in F2:
=IF(TIMEVALUE(D2)>=TIMEVALUE(E2&":00"),TIMEVALUE(D2)-TIMEVALUE(E2&":00"),TIMEVALUE(E2&":00")-TIMEVALUE(D2))

format cell as:
[h]:mm

Copy down...

Regards,
 
hi khalid sb
plz find attached...req results r in column I & J....in column I these values r in minus and in column J these r positve....i wnt these results with one formula with sign only (-)..in column F..
 

Attachments

  • Sample File.xlsx
    24.1 KB · Views: 0
Nabeel said:
hi khalid sb
plz find attached...req results r in column I & J....in column I these values r in minus and in column J these r positve....i wnt these results with one formula with sign only (-)..in column F..
Click to expand...
See this file,
Please note (-) values are not in real numbers.

Regards,
 

Attachments

  • Time subtract2.xlsx
    24.7 KB · Views: 0
ohh i really sorry mr khalid..its my mistake to share it..
the value is which is in column D less then is 234 it should be in minus and value in column D is greater thn 234 it should be in positve..plz also see the last values which is showing error.it should also shows result.thnx a lot
BR
 
Nabeel said:
ohh i really sorry mr khalid..its my mistake to share it..
the value is which is in column D less then is 234 it should be in minus and value in column D is greater thn 234 it should be in positve..plz also see the last values which is showing error.it should also shows result.thnx a lot
BR
Click to expand...
Its okay, no problem...

so, change the formula as:
=IF(D2-E2<0,TEXT(E2-D2,"-[H]:MM"),D2-E2)

and i think the last value is showing error is because it's time value = "0:00"
 
Nabeel said:
salam Mr khalid Sb
can you solve last error.there is any Technic or solution
BR
Click to expand...
W/Salam Nabeel,
I am still not sure, as the results are text values :confused:
Enter this in F2 and copy down:
=IFERROR(IF(D2-E2<0,TEXT(E2-D2,"-[H]:MM"),D2-E2),E2-(MID(D2,FIND(":",D2)-2,2)&":00"))

Regards,
 
salam & good day khalid sb
you are brilliant & master.its working but only negative sign is not showing..(only with that specific error) other all values are correct.
BR
 
Nabeel said:
salam & good day khalid sb
you are brilliant & master.its working but only negative sign is not showing..(only with that specific error) other all values are correct.
BR
Click to expand...
Walekum as Salam Nabeel,
Thank you for the words, I am not that kind of.
All i learned here, true masters & brilliants are the ninjas here.

for the Minus sign you can try this:
=IFERROR(IF(D2-E2<0,TEXT(E2-D2,"-[H]:MM"),D2-E2),TEXT(E2-(MID(D2,FIND(":",D2)-2,2)&":00"),"-[H]:MM"))

Regards,
 
dear Mr khalid sb
u r master for me....
u have solved the problem.bundles of thnx & may more need ur expert help in future as well....can i put conditional formatting to show minus values in red..
BR
 
Nabeel said:
dear Mr khalid sb
u r master for me....
u have solved the problem.bundles of thnx & may more need ur expert help in future as well....can i put conditional formatting to show minus values in red..
BR
Click to expand...
:)
CF can be used, but not for minus values, as there are no minus values but the text values...!

you can use the following:
Select the F2:F136
Go to Conditional Formatting > New Rule > Use a Formula:

=ISTEXT(F2)
Set the format to your need.

Regards,

Khalid
 
You must log in or register to reply here.
Back
Top