Solved Days360 is bringing back negative numbers

Microsoft Excel 010 - complete package
May 29, 2013 at 09:07:01
Specs: Windows XP
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!


See More: Days360 is bringing back negative numbers

Report •


#1
May 29, 2013 at 09:57:50
✔ Best Answer
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.


Report •

#2
May 29, 2013 at 10:03:45
Thank you, that worked perfectly! Wish I'd thought of that myself!

Report •

#3
May 29, 2013 at 10:37:16
Be careful how you use the =DAY360() function,
it has Two additional arguments: True & False

DAYS360 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 method

If your data looks like:

         A
1) 09/01/2012
2) 08/31/2013

If 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

http://www.skeptic.com/


Report •

Related Solutions

#4
May 29, 2013 at 10:53:54
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?


Report •

#5
May 29, 2013 at 11:10:26
so I don't end up with 5,000 cells with 01/00/1900 in them

What 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

http://www.skeptic.com/


Report •

#6
May 29, 2013 at 12:23:43

so I don't end up with 5,000 cells with 01/00/1900 in them What 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.


Report •

#7
May 29, 2013 at 12:51:13
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...

Might save you some gray hairs in the future.

MIKE

http://www.skeptic.com/


Report •

#8
May 29, 2013 at 14:00:30
The cells are NOT empty if they display the date 01/00/1900,
the cell contains the number zero.

All that's visible in 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.


Report •

#9
May 29, 2013 at 14:04:52
Btw, this was my solution

.....IF(E2=0,"",IF(G2=0,DAYS360(E2,TODAY()),DAYS360(E2,G2)))

E2=created date
G2=assigned date


Report •

#10
May 29, 2013 at 14:06:06
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

http://www.skeptic.com/


Report •

#11
May 29, 2013 at 14:16:49
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

http://www.skeptic.com/


Report •

#12
May 29, 2013 at 14:33:53
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.


Report •

#13
May 29, 2013 at 18:56:23
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

http://www.skeptic.com/


Report •

#14
May 30, 2013 at 06:50:44
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!


Report •

#15
May 31, 2013 at 06:50:11
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

http://www.skeptic.com/


Report •

#16
June 3, 2013 at 05:10:05
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!


Report •

#17
June 3, 2013 at 12:59:03
...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/13

So =G14-E14
will give you the number of days: 1

Now, 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

http://www.skeptic.com/


Report •

#18
June 3, 2013 at 13:06:05
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?


Report •

#19
June 3, 2013 at 14:02:24
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
and recalculate 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

http://www.skeptic.com/


Report •

#20
June 3, 2013 at 14:08:07
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?

Report •

#21
June 3, 2013 at 14:23:22
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

http://www.skeptic.com/


Report •

#22
June 3, 2013 at 14:45:05
Yes, thanks for the info.

Report •

#23
June 3, 2013 at 17:04:32
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.


Report •


Ask Question