Prev: how to arrange list by first or last name
Next: Algrebraic Functions
From: da on 27 May 2010 15:59
Hello
How can I convert the following numbers into hrs:min?
I have tried every possible format (including custom) and I do not get the
results I want (e.g. 15:45 18:40)
I want to get the difference in time between these numbers.
Thank you

1545 1840
2015 2300
2045 2105
1700 1815
1825 1840
1850 2000
1835 1900

From: Nate on 27 May 2010 16:51
Assuming your values are in columns A & B, I used this formula in C1 to get
the result you are looking for:

=TEXT((LEFT(B1,2)&":"&RIGHT(B1,2))-(LEFT(A1,2)&":"&RIGHT(A1,2)),"HH:MM")

Using your sample data, these are the results:

1545 1840 02:55
2015 2300 02:45
2045 2105 00:20
1700 1815 01:15
1825 1840 00:15
1850 2000 01:10
1835 1900 00:25

Hope that helps!

"da" wrote:

> Hello
> How can I convert the following numbers into hrs:min?
> I have tried every possible format (including custom) and I do not get the
> results I want (e.g. 15:45 18:40)
> I want to get the difference in time between these numbers.
> Thank you
>
> 1545 1840
> 2015 2300
> 2045 2105
> 1700 1815
> 1825 1840
> 1850 2000
> 1835 1900
>
From: Billy Liddel on 27 May 2010 18:11
Nate

Your formula gives the correct result, but as text. Could use:

=VALUE(TEXT((LEFT(B1,2)&":"&RIGHT(B1,2))-(LEFT(A1,2)&":"&RIGHT(A1,2)),"HH:MM"))

then format the number as time

Regards
Peter

"Nate @ CST Global" wrote:

> Assuming your values are in columns A & B, I used this formula in C1 to get
> the result you are looking for:
>
> =TEXT((LEFT(B1,2)&":"&RIGHT(B1,2))-(LEFT(A1,2)&":"&RIGHT(A1,2)),"HH:MM")
>
> Using your sample data, these are the results:
>
> 1545 1840 02:55
> 2015 2300 02:45
> 2045 2105 00:20
> 1700 1815 01:15
> 1825 1840 00:15
> 1850 2000 01:10
> 1835 1900 00:25
>
> Hope that helps!
>
> "da" wrote:
>
> > Hello
> > How can I convert the following numbers into hrs:min?
> > I have tried every possible format (including custom) and I do not get the
> > results I want (e.g. 15:45 18:40)
> > I want to get the difference in time between these numbers.
> > Thank you
> >
> > 1545 1840
> > 2015 2300
> > 2045 2105
> > 1700 1815
> > 1825 1840
> > 1850 2000
> > 1835 1900
> >
 | 
Pages: 1
Prev: how to arrange list by first or last name
Next: Algrebraic Functions