# Thread: Time calculation (XP)

1. ## Time calculation (XP)

Writing this spreadsheet and one of the calculations is arrival time from one flight and the departure time of another.
In cell B:21 I have the arrival time.
In cell F20 I have the departure time.
In cell F24 I have =(MINUTE(E24))+(HOUR(E24)*60) and in E25
=IF(F24<=120,"transfer time could create problems","")

My problem is cell E24. All I need to do is extract the departure time from the arrival time. I've tried all sorts of IF formulas but it just doesn't work

We need more then 2 hours transfer time, that's why the message "transfer time could create problems" comes up in red when its less then 120 minutes.

Thanks

2. ## Re: Time calculation (XP)

Hi Karat

I am trying to simulate your description in an excel worksheet and getting a circular reference.

Is there a chance you can add a sample workbook to the next post so I can see how you have it layed out please

3. ## Re: Time calculation (XP)

Have attached the section. As most of the flight info is linked, I have copies and paste special the values

When I need is a formula that works

I've tried and tried and it just won't work.

Thanks so much for your help

4. ## Re: Time calculation (XP)

I have had a look and understanding you have stripped some data out I added some data manually to A2 and D2 to simulate an arrival and departurs date and it seems to be working. I also changed the format of E24 to Time/Date and it seems to match

5. ## Re: Time calculation (XP)

It doesn't work. I have to use the 12 hour clock. No-one here uses the 24 hour clock, so if I use 15:00 I will be asked to change that to 3pm.

I think the am/pm is my problem.

Thanks

6. ## Re: Time calculation (XP)

Got you, then we can easily adjust my previous workbook <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

You just need to change the format of of the cells. Highlight cell A2 , B2 and E24 seperately and change the format of the cells by clicking

Format --> Cells and then highlight the Custom category and type in h:mm AM/PM in the type field.

Now when you add times stipulate 4:00 AM and this will tell excel that it is 0400 if you type 4:00 PM it will be 1600.

HTH

7. ## Re: Time calculation (XP)

Your formula really helped. I deleted all the times and re-entered them setting the format 12 the 12 hour clock and entered them as 24 hour clock

So thanks for the formula.. It works now

Thanks again

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•