display time in IST as well as GMT?

[hsg]

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?

 

[Huber57]

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.

 

[Fred Smith]

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]

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]

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.

 

[Ron@Buy]

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