Can someone look at this? IF(G14<>"",DAYS360(G14,E14),"")

G14 is the assigned date while E14 is the date it was created so if it was created on 5/23/13 and assigned to someone on 5/24/13, why does that formula show -1 as the age?

Anyone have any suggestions? I added '+1' to the end of the fomula and it answered -2. Then I added '-1' and it came back with a blank because I turned off the zeros. I am open to ideas, here!

If the date created will always be before the assigned date, all you need to do is switch the two date fields around in your formula to look like this: =IF(G14<>"",DAYS360(E14,G14),"")

Law of Logical Argument: Anything is possible if you don't know what you're talking about.

Thank you, that worked perfectly! Wish I'd thought of that myself!

Be careful how you use the =DAY360() function,

it has Two additional arguments: True & FalseDAYS360 returns the number of days between two dates based on a 360-day year

but there are two ways to calculate that.=DAYS360(A1,A2,TRUE) uses the US method

while

=DAYS360(A1,A2,FALSE) uses the European methodIf your data looks like:

A 1) 09/01/2012 2) 08/31/2013If you use the US formula:

=DAYS360(A1,A2,TRUE) you get 359 days

If you use the European formula:

=DAYS360(A1,A2,FALSE) you get 360 days

If you use no argument in the formula:

=DAYS360(A1,A2) you get 360 days.

The default is the European style.

If your looking to determine the number of days between two dates

I would advise looking at the =DATEDIF() function or

perhaps the =NETWORKDAYS() function.They may be better suited to your needs.

MIKE

Okay because this wb is puling back data from another wb, I have blanked out (turned off the zeros with Options) and have formatted the cells with mm/dd/yyyy;; so I don't end up with 5,000 cells with 01/00/1900 in them. Now, when I use IF(G2<>"",DAYS360(E2,G2),"") I'm getting values like -40806. Does anyone know why and how to make it stop?

so I don't end up with 5,000 cells with 01/00/1900 in themWhat are you doing, or trying to do, that you end up with

5000 cells with 01/00/1900?You also might want to take a moment and read this Introduction to Dates:

http://www.cpearson.com/excel/datet...

It might help you better understand what's going on.

MIKE

so I don't end up with 5,000 cells with 01/00/1900 in themWhat are you doing, or trying to do, that you end up with

5000 cells with 01/00/1900?Thanks for the link, I'll look at it when I have time.

This spreadsheet is huge, 15,000 rows of data so far, and as I stated above

this wb is puling back data from another wb,the one I'm working in being the managers' copy with statistics referring to each case (which is a row of entries.)Because there are links in every single cell (so value in the cell), calling the data from the other version, and the cells are formatted for dates, Excel shows the default of 01/00/1900 if there are empty cells....and since every case is not assigned (

IF(G14<>"",DAYS360(G14,E14),"") G14 is the assigned date while E14 is the date it was created so if it was created on 5/23/13 and assigned to someone on 5/24/13) a note from my first post, I need to count days between created and assigned dates but do nothing if there is no assigned date.Now I'm thinking about a Countif looking for the 01/00/1900...and am interested in and would appreciate suggestions along those lines.

Excel shows the default of 01/00/1900 if there are empty cellsThe cells are

NOTempty if they display the date 01/00/1900,

the cell contains thenumber zero.Just as a guess:

Your cell G14, from your DAYS360 formula

is using a cell which was imported from the other WB,

containing a zero and your getting a mysterious five digit number?If this is correct, then your test in the formula:

IF(G14<>""

is what is causing the problem, because the cell is not null,

it contains a number zero.Try specifying a zero:

IF(G14<>"0"

see if that works.As for the mysterious five digit number, take some time

to learn something new and read:http://www.cpearson.com/excel/datet...

Might save you some gray hairs in the future.

MIKE

The cells are NOT empty if they display the date 01/00/1900,

the cell contains the number zero.All that's

visiblein the cell on the manager's wb is the formula and that's only in the formula bar when the cell is selcted. When I check the filters, there is a selection for 01/00/1900 listed. When I select that, the rows that look empty (but contain the formula to the empty cell in the user book) show up. (The corresponding cell on the user wb is empty, unused, there is nothing there, it has no value, not even a spacebar space.)Until I added the dd/mm/yyyy;; to the custom formatting all of those visibly empty cells had that 1900 date showing, though there is nothing in the corresponding cell in the user workbook.

As for the

mysterious five digit number?, we're looking at the count between 01/00/1900 (which is day 0) and whatever other date is in question.

Excel shows the default of 01/00/1900 if there are empty cells

The cells are NOT empty if they display the date 01/00/1900,

the cell contains the number zero.

Just as a guess:

Your cell G14, from your DAYS360 formula

is using a cell which was imported from the other WB,

containing a zero and your getting a mysterious five digit number?

If this is correct, then your test in the formula:

IF(G14<>""

is what is causing the problem, because the cell is not null,

it contains a number zero.

Try specifying a zero:

IF(G14<>"0"

see if that works.

As for the mysterious five digit number, take some time

to learn something new and read:

http://www.cpearson.com/excel/datet..I appreciate your time but there was no need to be condescending.

Btw, this was my solution .....IF(E2=0,"",IF(G2=0,DAYS360(E2,TODAY()),DAYS360(E2,G2)))

E2=created date

G2=assigned date

no need to be condescending.I did not mean to come off sound like that.

I know that dealing with Dates in Excel can be

a frustrating exercise.MIKE

I have blanked out (turned off the zeros with Options)Simply because you cannot see the zero in the cell,

does not mean it is not there.Try this,

If cell G14 "appears" empty look at the formula bar,

if you see a zero, then the cell is not null.Also, try the formula =ISBLANK(G14)

if it comes back TRUE then the cell is really null

if it comes back FALSE then there is something

contained in the cell.You could alos try =ISNUMBER(G14)

if it comes back TRUE then the cell contains a number,

and zero is a number.How are you transferring the data from one WP to the other?

MIKE

Understood.

When I select that, the rows that look empty (but contain the formula to the empty cell in the user book) show up. (The corresponding cell on the user wb is empty, unused, there is nothing there, it has no value, not even a spacebar space.)Checking the formula bar in the user wb, the corresponding cell has nothing in it, and that's with me clicking into the formula bar itself and hitting backspace because I have dealt with cells that look empty have actually have spaces in them. (Yuck!)

And the values are being transferred with a basic link, = followed by the path name\folder name\wb name\ws name!cell reference. So, this: ='J:\DeptData\CommercialAccountOpening\1 - Queue Management\Master Versions\[CDD All PODs Assignment List.xlsx]All PODs'!D3537

I have thought about adding an IF in there, so if the cell is empty (the user wb cell) it doesn't populate which might save me from the 01/00/1900 but just haven't done it since I'm still dealing with other pieces.

Thanks again for your time. It is appreciated.

I played around with this for a while, and the way the WB's are linked seems to be

the issue.

No matter what I tried, null cells in one WB always get input as zero in the other.Do you have to do it this way?

Could you not simply do a Copy / Paste / Special / Values

from one WB to the other?

That would solve your Zero problem.IF(E2=0,"",IF(G2=0,DAYS360(E2,TODAY()),DAYS360(E2,G2)))

Not sure this will work, as the TODAY() function is volatile.

When you open the WB tomorrow, it will use tomorrows

date, because tomorrow will be today, if you see what I mean.

So all your formula get recalculated with the new date.MIKE

Could you not simply do a Copy / Paste / Special / Values

from one WB to the other?No, this is a manager's copy of a workbook that the employees use pretty much all day. If he opens it at 9am, it's going to look different than it 8am so every single time he opened it, he'd have to copy and paste. Having it linked, if he closes and reopens, he's getting the freshest data and all updates. Short of building a database with Access...well, linking seemed like the best idea.

And glad you got to see that null to zero issue with empty cells yourself! Annoying, huh? Welcome to my world, lol!

Are you still intent on using a formula with the =DAY360() function? Because if you are, you will be getting errors very soon, if you haven't

already.MIKE

I'm using this formula IF(G2=0,DAYS360(E2,TODAY()),DAYS360(E2,G2)) ...and it's doing okay for now. I might not need this for long so it's usable.

Thanks!

...and it's doing okay for now.OK, but I still think it is a bit much

when all you need do to get the number of days

between two dates is simply subtract:E14 is the date it was created 5/30/13

G14 is the assigned date 5/31/13So =G14-E14

will give you the number of days: 1Now, as for the DAYS360() function, it will:

Shows the number of days between two dates

based on a 360-day year (twelve 30-day months).Make note of the last three words.....

so if we use the same dates as shown above,

in the formula: =DAYS360(E14,G14)

can you guess what your result will be?MIKE

E14 is the date it was created 5/30/13

G14 is the assigned date 5/31/13

So =G14-E14

will give you the number of days: 1

But if G14 is empty and I want to use today as the date, then what?

I want to use today as the date, then what?Then use TODAY() as your second day.

All your doing is dropping the DAYS360 function:=IF(G2=0,TODAY()-E2,G2-E2)

Just make sure your cell is formatted as Number.

The DAYS360() swaps the Dates around, it uses

=DAYS360(Create Date,Assign Date)

while doing the regular date math, the sequence is

Assign Date minus Create Date.

But also understand that using the TODAY() function is not advised,

Every DAY you open your workbook, the TODAY() function automatically

calculates your formulas using TODAY'S date.

So:

Today when you open your work book it will use: June 03, 2013 as the date

Tomorrow when you open your work book it will use June 04, 2013 as the date

The Day After Tomorrow when you open your work book it will use June 05, 2013

andrecalculate all your formulas each day,

so every day you will get a brand new number of days between Create and Assign.I don't think that is what your looking for.

MIKE

Actually, if the assigned date is still empty tomorrow, then yes, I do want a new formula with a new date. In this case I'm checking for how long it take for something that came in on May 1st, for instance, to be assigned. If the date is empty tommorrow of next week, the Today() will be a different day, right?

If the date is empty tommorrow of next week, the Today() will be a different day, right?Correct, so, yes, it could be used to track how many days between Create & Assigned.

As long as your aware of how the TODAY() function works.

It will not input a static date into a cell, you have to do that by hand.MIKE

Yes, thanks for the info.

As long as your aware of how the TODAY() function works.

It will not input a static date into a cell, you have to do that by hand....or use a very simple macro.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History