help needed with excell formula

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

rama4672

Thread Starter
Joined
Jun 1, 2003
Messages
84
What i want to do is take a start date and then get it to count the number of days between the end date

IE: A1 would be 12/09/03 A2 would be the end date and A5 would hold the formula.
So if a chart was issued on 12/09/03 it would count the number of days and display in A5 until the chart was returned and the date of return was put in col A2 then A5 Should be blank.


Tia
Rama
 
Joined
Aug 17, 2003
Messages
17,584
In A5 simply subtract A1 from A2.
Format A5 as a number, not as a date.

Excel handles date arithmetic fine, subtracting 1 date from another gives the number of days.

Just re-format the cell where the formula is, as it will take on a date format by default

I cant quite comprehend the fine detail you want following that.
 
Joined
Aug 17, 2003
Messages
17,584
In A5:

=IF(A2="",INT((NOW()-A1)),"")

Will put the number of days between the date in A1 and NOW(), being todays date and time.

As soon as A2 has any entry in it, A5 goes blank.
 

rama4672

Thread Starter
Joined
Jun 1, 2003
Messages
84
thanks for the quick reply. but there is no date in A2 to take away, the charts are issued to a driver and the date put in col A1 then they have 21 days to return the charts back to us, so it must calculate how many days has passed since being issued with the chart.
When the chart is returned we then put the return date in A2 and then A5 should be blank so that we know that the chart has been returned

Tia

Rama
 

rama4672

Thread Starter
Joined
Jun 1, 2003
Messages
84
thanks for the reply kiwiguy it seems you sent your second reply as i was replying to your first

Thanks again

Rama
 
Joined
Feb 14, 1999
Messages
11,746
First Name
Anne
=IF(ISBLANK(A1),"",NOW()-A1)

It probably does the same as KiwiGuy, but might be easier to understand for us non-mathematicians.

Sorry, kiwiguy. I feel like I'm picking on you! I'm not! I swear!
:D
 
Joined
Aug 17, 2003
Messages
17,584
Your answer may give unwanted decimal places, as NOW() includes the time.

=IF(ISBLANK(A1),"",INT(NOW()-A1)) may work better. (E&OE)

You picking on me?
Never.
Kiwis may be endangered species, but they have a sense of humo(u)r and a thick skin.

Cheers
 
Joined
Feb 14, 1999
Messages
11,746
First Name
Anne
ROFL!!

For the record: formatting the cell gets rid of unwanted decimals too, and he doesn't want to return a date/time, he needs a number of days.

More importantly, when I was in the Navy, I took a "jump test" so I could go flying from time to time with my bosses, who were pilots. We had to jump out of this dead airplane and onto a trampoline-kinda-thing. Really stupid. But it was called the Kiwi test. :) I got to go flying twice in a T34-C. WAY cool. And I did not get sick!
 
Joined
Aug 17, 2003
Messages
17,584
Because Kiwis dont fly I guess?

We do that a lot down here, but with bungy cords attached.
No wimpy Trampolines!

Now, jumping out of a flying plane onto a trampoline, thats more likely to be an achievement.

You would make your mark that way.
 

rama4672

Thread Starter
Joined
Jun 1, 2003
Messages
84
thanks for the input guys... that works OK but when a date is added in to cell A2 i need the cell that holds the formula to be left blank


Tia

Rama
 
Joined
May 4, 2002
Messages
743
Dreamboat and the Flying Kiwi's:

What is the world's preocupation with NOW ? I hate using it unless I have to have the time. Why not DBAs formula:

=IF(ISBLANK(A1),"",TODAY()-A1)


and Rama....what you you mean by the last response?
The above formula should leave cell A3 blank until a date is added. What is it then that you want?

I was understand - but now are cornfused!

- Castleheart :confused:
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top