I need to display time in two cells in different format

a1 = indian standard time
b1=corresponding GMT

example:  a1=06:00   then   b1=00:30 (IST = +5:30 GMT)
                  a1=02:00   then   b1=20:30

Is there a formula which will do the trick?

Furthermore, I want to enter IST in cell A1 by simply entering 0600 or 0200, 
can this be automatically
formatted to display 06:00 or 02:00?

hsg,

Enter the following formula in b1:

=A1-((1/24)*5.5)

Then highlight all the cells you want to appear as 00:00

Go to Format Cells (Ctrl+1) and select custom.  From there, type in hh:mm.

In order for this to work you must enter a full date plus the time e.g. 
2/26/2010 6:00 AM.  When you format it as hh:mm the date goes away but is 
still available to calculate back a day.

HTH.

"hsg" wrote:

> I need to display time in two cells in different format
> 
> a1 = indian standard time
> b1=corresponding GMT
> 
> example:  a1=06:00   then   b1=00:30 (IST = +5:30 GMT)
>                   a1=02:00   then   b1=20:30
> 
> Is there a formula which will do the trick?
> 
> Furthermore, I want to enter IST in cell A1 by simply entering 0600 or 0200, 
> can this be automatically
> formatted to display 06:00 or 02:00?

B1: =a1-time(5,30,0)

A format cannot change an entry of 0600 into a time of 06:00. You can with a 
formula like:
=time(int(a1/100),mod(a1,100),0)

Regards,
Fred

"hsg" <hsg@discussions.microsoft.com> wrote in message 
news:0D109666-8308-418C-9C26-EF449D9D3104@microsoft.com...
>I need to display time in two cells in different format
>
> a1 = indian standard time
> b1=corresponding GMT
>
> example:  a1=06:00   then   b1=00:30 (IST = +5:30 GMT)
>                  a1=02:00   then   b1=20:30
>
> Is there a formula which will do the trick?
>
> Furthermore, I want to enter IST in cell A1 by simply entering 0600 or 
> 0200,
> can this be automatically
> formatted to display 06:00 or 02:00? 

Thanks, but it is difficult to enter full time & date.

I am working out a logic like: "I know the time difference is 5.50 hrs, so 
If my
current time entered is less than 5:30 hrs, I am actually looking to add 
(24-5.5) hours to get the correct figure. However I have not been able to 
work out the
format part which will display 1015 as 10:15, but perform calculations as if 
1015
is 10 hrs 15 minutes

any simple idea?

hsg

"Huber57" wrote:

> hsg,
> 
> Enter the following formula in b1:
> 
> =A1-((1/24)*5.5)
> 
> Then highlight all the cells you want to appear as 00:00
> 
> Go to Format Cells (Ctrl+1) and select custom.  From there, type in hh:mm.
> 
> In order for this to work you must enter a full date plus the time e.g. 
> 2/26/2010 6:00 AM.  When you format it as hh:mm the date goes away but is 
> still available to calculate back a day.
> 
> HTH.
> 
> "hsg" wrote:
> 
> > I need to display time in two cells in different format
> > 
> > a1 = indian standard time
> > b1=corresponding GMT
> > 
> > example:  a1=06:00   then   b1=00:30 (IST = +5:30 GMT)
> >                   a1=02:00   then   b1=20:30
> > 
> > Is there a formula which will do the trick?
> > 
> > Furthermore, I want to enter IST in cell A1 by simply entering 0600 or 0200, 
> > can this be automatically
> > formatted to display 06:00 or 02:00?

hsg,

a couple of points.

I don't know your application but it is very easy for excel to enter the 
current date (=TODAY()) or the current date/time (=NOW()).

to figure out how to display the 10:15 you need to know how excel treats 
days and hours, etc.  Each day is worth 1.  so each hour would be equal to 
1/24.    To get excel to return 10:15 you would take =(1/24)*10.25 (the .25 
representing a 1/4 of an hour or 15 min).  Then format the cells as above 
(hh:mm).

This is a key concept to understand.  Once you have that down, it become 
easier to manipulate times and dates.

HTH.

"hsg" wrote:

> Thanks, but it is difficult to enter full time & date.
> 
> I am working out a logic like: "I know the time difference is 5.50 hrs, so 
> If my
> current time entered is less than 5:30 hrs, I am actually looking to add 
> (24-5.5) hours to get the correct figure. However I have not been able to 
> work out the
> format part which will display 1015 as 10:15, but perform calculations as if 
> 1015
> is 10 hrs 15 minutes
> 
> any simple idea?
> 
> hsg
> 
> "Huber57" wrote:
> 
> > hsg,
> > 
> > Enter the following formula in b1:
> > 
> > =A1-((1/24)*5.5)
> > 
> > Then highlight all the cells you want to appear as 00:00
> > 
> > Go to Format Cells (Ctrl+1) and select custom.  From there, type in hh:mm.
> > 
> > In order for this to work you must enter a full date plus the time e.g. 
> > 2/26/2010 6:00 AM.  When you format it as hh:mm the date goes away but is 
> > still available to calculate back a day.
> > 
> > HTH.
> > 
> > "hsg" wrote:
> > 
> > > I need to display time in two cells in different format
> > > 
> > > a1 = indian standard time
> > > b1=corresponding GMT
> > > 
> > > example:  a1=06:00   then   b1=00:30 (IST = +5:30 GMT)
> > >                   a1=02:00   then   b1=20:30
> > > 
> > > Is there a formula which will do the trick?
> > > 
> > > Furthermore, I want to enter IST in cell A1 by simply entering 0600 or 0200, 
> > > can this be automatically
> > > formatted to display 06:00 or 02:00?

Not quite what you are looking for but a posible work around:
Use a blank cell (say H1) to enter your time > Format as Text (this will 
allow you to enter your time with a preceeding 0 i.e 0600)
Then Format A1 >Time
Enter formula in A1 > =LEFT(H1,2)&":"&RIGHT(H1,2)
Format B1 > Custom >[hh]:mm
Enter formula in B1 > 
=IF(A1-((1/24)*5.5)<0,(1/24)*24-((1/24)*5.5)+A1,A1-((1/24)*5.5))
Now when you enter an IST into H1 (say 0245) A1 will show 02:45 and B1 21:15
However be aware that there is no error checking on the time that is entered 
into H1





"hsg" wrote:

> Thanks, but it is difficult to enter full time & date.
> 
> I am working out a logic like: "I know the time difference is 5.50 hrs, so 
> If my
> current time entered is less than 5:30 hrs, I am actually looking to add 
> (24-5.5) hours to get the correct figure. However I have not been able to 
> work out the
> format part which will display 1015 as 10:15, but perform calculations as if 
> 1015
> is 10 hrs 15 minutes
> 
> any simple idea?
> 
> hsg
> 
> "Huber57" wrote:
> 
> > hsg,
> > 
> > Enter the following formula in b1:
> > 
> > =A1-((1/24)*5.5)
> > 
> > Then highlight all the cells you want to appear as 00:00
> > 
> > Go to Format Cells (Ctrl+1) and select custom.  From there, type in hh:mm.
> > 
> > In order for this to work you must enter a full date plus the time e.g. 
> > 2/26/2010 6:00 AM.  When you format it as hh:mm the date goes away but is 
> > still available to calculate back a day.
> > 
> > HTH.
> > 
> > "hsg" wrote:
> > 
> > > I need to display time in two cells in different format
> > > 
> > > a1 = indian standard time
> > > b1=corresponding GMT
> > > 
> > > example:  a1=06:00   then   b1=00:30 (IST = +5:30 GMT)
> > >                   a1=02:00   then   b1=20:30
> > > 
> > > Is there a formula which will do the trick?
> > > 
> > > Furthermore, I want to enter IST in cell A1 by simply entering 0600 or 0200, 
> > > can this be automatically
> > > formatted to display 06:00 or 02:00?