=VALUE(LEFT(B1,2) & ":" & RIGHT(B1,2))+(VALUE(LEFT(A1,2) & ":" & RIGHT(A1,2))>VALUE(LEFT(B1,2) & ":" & RIGHT(B1,2)))-VALUE(LEFT(A1,2) & ":" & RIGHT(A1,2))
Where B1 is the "To" time. Format result as required.
See Excel Date and Times
Compare Excel | Excel Templates | DownloaderXL Pro
Finance Templates & Add-In Bundle | NeuroXL Predictor | Construction Estimating
Merge Excel The Easy Way | Trading Add-ins For Excel | Convert Excel Into Calculating Web Pages
Excel Web Pages | Produce Clean Efficient VBA Code Every Time | Build Automated Trading Models In Excel | Excel Web Pages | Excel Video Training
I just want to shear a formula which will help if you to calculate time stored as text for example
from | to |worked
1800 | 2400| 6
I searched the forum earlier and could not find anything simular
please note that the cell format for column A and B is custom 0000 in case values like 0100 have to be entered
the formula placed in c1 is:
=(INT(a1/100)+((a1-INT(a1/100)*100)/60))-(INT(B1/100)+((B1-INT(B1/100)*100)/60))
=VALUE(LEFT(B1,2) & ":" & RIGHT(B1,2))+(VALUE(LEFT(A1,2) & ":" & RIGHT(A1,2))>VALUE(LEFT(B1,2) & ":" & RIGHT(B1,2)))-VALUE(LEFT(A1,2) & ":" & RIGHT(A1,2))
Where B1 is the "To" time. Format result as required.
See Excel Date and Times
Last edited by Dave Hawley; December 14th, 2006 at 14:39.
Hi,
Try,
=--TEXT(B1,"00\:00")-TEXT(A1,"00\:00")+0+(TEXT(A1,"00\:00")+0<TEXT(B1,"00\:00")+0)
Format the cell as hh:mm
HTH
KK is there any reson for the double -- and the 0 in your formula.
Originally Posted by Krishnakumar
both of them converts text value to number value
=TEXT(B1,"00\:00")+0-TEXT(A1,"00\:00")+0+(TEXT(A1,"00\:00")+0<TEXT(B1,"00\:00")+0)
or
=--TEXT(B1,"00\:00")-(--TEXT(A1,"00\:00"))+--(TEXT(A1,"00\:00")+0<TEXT(B1,"00\:00"))
Last edited by Krishnakumar; December 14th, 2006 at 19:09.
This seems similar ..... found it at timecardworkbook.com - if the 4 digit military time is in cell A1 and this formula is in B1:
=IF(A1="",0,+VALUE(+CONCATENATE(+(A1-+RIGHT(A1,2))/100,":",+RIGHT(A1,2))))this worked for me!Thanks for the forum ....
Hi
Another version which might be of helpl! =(--(LEFT(B1,2)&":"&RIGHT(B1,2)))-(--(LEFT(A1,2)&":"&RIGHT(A1,2)))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks