Calling MS Excel Guru's

[Wildduke]

hey guys, I am working on my excel logbook and have stumbled across somthing that I dont know how to do. I have tried searching google for help, but I cant find anything specific to what i'm trying to do.

Basically, I want to write a formula so that IF I have a value in a box lets say A1 then I want excel to copy the data from B1 into B2.

So I know that I have to write this formula in B2 using the IF statement, however can any of you help me out writing the correct formula?

Many thanks for all your help!

[john_tullamarine]

Try, in B2

=IF(A1="","",B1)

This looks at A1 to see if A1 is empty, in which case make B2 empty, otherwise copy B1 across to B2.

This doesn't care what the cell contents might be so, depending on what you are doing and how you are formatting, you might need to make the expression a bit flashier .. come back to us if the vanilla version doesn't do what you are after ..

Depending on how you are setting up your spreadsheet, the array functions may prove to be very useful to you as well.

[john_tullamarine]

Beaten to the answer

Never mind .. beers are on me.

[The_Pharoah]

the 'IF' formula is an excellent one and is quite easy to use as long as you understand how the formula works ie:

- basic formula is =IF(what the test is,what you want to do if true,what you want to do if false).

so for the solution provided --> =IF(A1="","",B1)

what the test is --> A1="" ("" is what you put when there's a blank)
what to do if true --> "" (ie. show blank)
what to do if false --> B1 (show whatever is in that cell).

Give a man a fish...you feed him for a day. Teach a man to fish...you feed him for a lifetime.

clear as mud?

[Chronic Snoozer]

Does anyone know if you can use IF to create a conditional format? The conditional format function only allows certain, simple arguments.

[AvEnthusiast]

But I would like excel to change the 4 digit numbers when I enter them in ATD and ATA to time format and the total actual time column get the difference of ATD and ATA and show me the total block time. how to do that?

[Gen. Anaesthetic]

Format the cells correctly and it's a cinch

Aventhusiast,

Before you enter the cells you're entering times into, make sure you format the cells (Format menu > cells. In the "number" area, select "Time" and use the 24 hour format) as time numbers. Same thing for cells that you are going to use to calculate the time difference. Then it's just a matter of subtracting the ATD from the ATA and you have the time difference in time format.

When you're entering the times, just make sure they're in the right format, so for example, 20:25 rather than 2025. You can do it with the latter but you would have to use a different process. Also if the ATA is after midnight of the following day (e.g. departed 22:30, arrived 00:30), enter the ATA as 24:30 or you'll end up with a negative number. You can use a formula here to get around this if you want to, such as =if(ATA-ETA<0,ATA-ETA+24,ATA-ETA). Obviously, replace ATA and ETA with the appropriate cell references.

This is just how I do it. I'm sure there are many other ways.

Hope it helps.

[AvEnthusiast]

Dearn Gen. A.,
Thanks for your inputs. I have already done that I mean I have chosen the format to date and time my point was the second choice if there are possiblities to enter 2025 and it changes it to 20:25 because now if you enter 2025 it changes it to a system generated time and and this all those 1904 date format things and ....

[dueweno]

A work around is to enter the four digit value as text seperate them into hours and minutes using the left, right function do the calculations and rejoining them with =cell&"":""&cell

[das Uber Soldat]

morning.

Ill attach a copy of my latest excel logbook program when I get to work, it might be useful to go over as I've had to overcome all these hurdles myself

[Mad (Flt) Scientist]

Originally Posted by Chronic Snoozer

Does anyone know if you can use IF to create a conditional format? The conditional format function only allows certain, simple arguments.

You can use the full IF function to create a "state" value and dump that in a convenient hidden cell. Then make the conditional formatting look at the "state value" cell. That way you're effectively using the IF functionality in the hidden cell to drive the conditional formatting.

[das Uber Soldat]

here we go;

Download Flight and Duty Times Ver 5.1.xls - File Qube

See if anything in there is helpful

edit: sorry password for unlocking all the tables to view the source is 'pprune'