Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
What's Wrong with This?
Hey guys-
Sorry for the multi-post, but didn't know which group this belongs to... I have 2 columns for dates. Sometimes only one of the two has an entry in it. Sometimes they both do. Although, they're NEVER both blank. I am trying to create a function that will compare the 2 dates (if there's even 2 there), and find out which one is the earliest of the two. If one date is missing from one column, it will just use the other date as the entry. Once it finds the earliest date between them, I want it to count how many days from the earliest date till today's date. I thought I did this right, but, the results are returning a #VALUE! answer. Can someone see what's going on here and tell me how to make this work? Thanks! =IF(L2="", DAYS360(V2,TODAY())), IF(V2="", DAYS360(L2,TODAY())), IF(L2<V2, DAYS360(L2,TODAY())), IF(L2V2, DAYS360(V2,TODAY())), IF(L2=V2, DAYS360(L2,TODAY())) D |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
What's Wrong with This?
=TODAY()-MIN(L2,V2)
-- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "D" wrote in message news:z_oAc.5140$tC5.1091@fed1read02... Hey guys- Sorry for the multi-post, but didn't know which group this belongs to... I have 2 columns for dates. Sometimes only one of the two has an entry in it. Sometimes they both do. Although, they're NEVER both blank. I am trying to create a function that will compare the 2 dates (if there's even 2 there), and find out which one is the earliest of the two. If one date is missing from one column, it will just use the other date as the entry. Once it finds the earliest date between them, I want it to count how many days from the earliest date till today's date. I thought I did this right, but, the results are returning a #VALUE! answer. Can someone see what's going on here and tell me how to make this work? Thanks! =IF(L2="", DAYS360(V2,TODAY())), IF(V2="", DAYS360(L2,TODAY())), IF(L2<V2, DAYS360(L2,TODAY())), IF(L2V2, DAYS360(V2,TODAY())), IF(L2=V2, DAYS360(L2,TODAY())) D |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
What's Wrong with This?
Nope- doesn't work. I have a value of 7/1/04 in L2, and V2 is blank. The
result is ########. I also need this to be able to calculate the days in a negative number if the earliest date between the 2 dates is already past today's date... Got any ideas? Thanks! D "Bob Phillips" wrote in message ... =TODAY()-MIN(L2,V2) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "D" wrote in message news:z_oAc.5140$tC5.1091@fed1read02... Hey guys- Sorry for the multi-post, but didn't know which group this belongs to... I have 2 columns for dates. Sometimes only one of the two has an entry in it. Sometimes they both do. Although, they're NEVER both blank. I am trying to create a function that will compare the 2 dates (if there's even 2 there), and find out which one is the earliest of the two. If one date is missing from one column, it will just use the other date as the entry. Once it finds the earliest date between them, I want it to count how many days from the earliest date till today's date. I thought I did this right, but, the results are returning a #VALUE! answer. Can someone see what's going on here and tell me how to make this work? Thanks! =IF(L2="", DAYS360(V2,TODAY())), IF(V2="", DAYS360(L2,TODAY())), IF(L2<V2, DAYS360(L2,TODAY())), IF(L2V2, DAYS360(V2,TODAY())), IF(L2=V2, DAYS360(L2,TODAY())) D |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
What's Wrong with This?
"D" wrote...
Sorry for the multi-post, but didn't know which group this belongs to... Your terminology is incorrect. You crossposted, which is OK. I have 2 columns for dates. Sometimes only one of the two has an entry in it. Sometimes they both do. Although, they're NEVER both blank. I am trying to create a function that will compare the 2 dates (if there's even 2 there), and find out which one is the earliest of the two. If one date is missing from one column, it will just use the other date as the entry. Once it finds the earliest date between them, I want it to count how many days from the earliest date till today's date. I thought I did this right, but, the results are returning a #VALUE! answer. Can someone see what's going on here and tell me how to make this work? Thanks! =IF(L2="", DAYS360(V2,TODAY())), IF(V2="", DAYS360(L2,TODAY())), IF(L2<V2, DAYS360(L2,TODAY())), IF(L2V2, DAYS360(V2,TODAY())), IF(L2=V2, DAYS360(L2,TODAY())) Do you really need DAYS360? If not, and if L2 or V2 could be after today's date, then =ABS(TODAY()-MIN(L2,V2)) If you need DAYS360, =IF(TODAY()<MIN(L2,V2),DAYS360(TODAY(),MIN(L2,V2)) ,DAYS360(MIN(L2,V2),TODAY())) -- To top-post is human, to bottom-post and snip is sublime. |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
What's Wrong with This?
=max(TODAY(),MIN(L2,V2))-min(TODAY(),MIN(L2,V2))*if(MIN(L2,V2)Today(),-1,1)
-- regards, Tom Ogilvy "D" wrote in message news:LxpAc.5144$tC5.596@fed1read02... Nope- doesn't work. I have a value of 7/1/04 in L2, and V2 is blank. The result is ########. I also need this to be able to calculate the days in a negative number if the earliest date between the 2 dates is already past today's date... Got any ideas? Thanks! D "Bob Phillips" wrote in message ... =TODAY()-MIN(L2,V2) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "D" wrote in message news:z_oAc.5140$tC5.1091@fed1read02... Hey guys- Sorry for the multi-post, but didn't know which group this belongs to... I have 2 columns for dates. Sometimes only one of the two has an entry in it. Sometimes they both do. Although, they're NEVER both blank. I am trying to create a function that will compare the 2 dates (if there's even 2 there), and find out which one is the earliest of the two. If one date is missing from one column, it will just use the other date as the entry. Once it finds the earliest date between them, I want it to count how many days from the earliest date till today's date. I thought I did this right, but, the results are returning a #VALUE! answer. Can someone see what's going on here and tell me how to make this work? Thanks! =IF(L2="", DAYS360(V2,TODAY())), IF(V2="", DAYS360(L2,TODAY())), IF(L2<V2, DAYS360(L2,TODAY())), IF(L2V2, DAYS360(V2,TODAY())), IF(L2=V2, DAYS360(L2,TODAY())) D |
#6
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
What's Wrong with This?
ok- using the ABS version works, kind of. It counts the days, but doesn't
return a negative value. ie- the earliest date is already past 86 days ago- 3/24/04. So, I need this to show as a -86 days, instead of a positive 86 days. Is there a work-around for this? THANK YOU! D "Harlan Grove" wrote in message ... "D" wrote... Sorry for the multi-post, but didn't know which group this belongs to... Your terminology is incorrect. You crossposted, which is OK. I have 2 columns for dates. Sometimes only one of the two has an entry in it. Sometimes they both do. Although, they're NEVER both blank. I am trying to create a function that will compare the 2 dates (if there's even 2 there), and find out which one is the earliest of the two. If one date is missing from one column, it will just use the other date as the entry. Once it finds the earliest date between them, I want it to count how many days from the earliest date till today's date. I thought I did this right, but, the results are returning a #VALUE! answer. Can someone see what's going on here and tell me how to make this work? Thanks! =IF(L2="", DAYS360(V2,TODAY())), IF(V2="", DAYS360(L2,TODAY())), IF(L2<V2, DAYS360(L2,TODAY())), IF(L2V2, DAYS360(V2,TODAY())), IF(L2=V2, DAYS360(L2,TODAY())) Do you really need DAYS360? If not, and if L2 or V2 could be after today's date, then =ABS(TODAY()-MIN(L2,V2)) If you need DAYS360, =IF(TODAY()<MIN(L2,V2),DAYS360(TODAY(),MIN(L2,V2)) ,DAYS360(MIN(L2,V2),TODAY( ))) -- To top-post is human, to bottom-post and snip is sublime. |
#7
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
What's Wrong with This?
BTW- how do I know if I need DAYS360 or not? Most the dates will be AFTER
today's date, but, eventually will lapse over to being BEFORE todays date, so I need both instances... Thanks D "Harlan Grove" wrote in message ... "D" wrote... Sorry for the multi-post, but didn't know which group this belongs to... Your terminology is incorrect. You crossposted, which is OK. I have 2 columns for dates. Sometimes only one of the two has an entry in it. Sometimes they both do. Although, they're NEVER both blank. I am trying to create a function that will compare the 2 dates (if there's even 2 there), and find out which one is the earliest of the two. If one date is missing from one column, it will just use the other date as the entry. Once it finds the earliest date between them, I want it to count how many days from the earliest date till today's date. I thought I did this right, but, the results are returning a #VALUE! answer. Can someone see what's going on here and tell me how to make this work? Thanks! =IF(L2="", DAYS360(V2,TODAY())), IF(V2="", DAYS360(L2,TODAY())), IF(L2<V2, DAYS360(L2,TODAY())), IF(L2V2, DAYS360(V2,TODAY())), IF(L2=V2, DAYS360(L2,TODAY())) Do you really need DAYS360? If not, and if L2 or V2 could be after today's date, then =ABS(TODAY()-MIN(L2,V2)) If you need DAYS360, =IF(TODAY()<MIN(L2,V2),DAYS360(TODAY(),MIN(L2,V2)) ,DAYS360(MIN(L2,V2),TODAY( ))) -- To top-post is human, to bottom-post and snip is sublime. |
#8
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
What's Wrong with This?
=Abs(TODAY()-MIN(L2,V2))*if(MIN(L2,V2)Today(),-1,1)
or =Abs(TODAY()-MIN(L2,V2))*Sign(Today()-MIN(L2,V2)) It doesn't sound like you need days360 -- Regards, Tom Ogilvy "D" wrote in message news:AkqAc.5165$tC5.3450@fed1read02... BTW- how do I know if I need DAYS360 or not? Most the dates will be AFTER today's date, but, eventually will lapse over to being BEFORE todays date, so I need both instances... Thanks D "Harlan Grove" wrote in message ... "D" wrote... Sorry for the multi-post, but didn't know which group this belongs to... Your terminology is incorrect. You crossposted, which is OK. I have 2 columns for dates. Sometimes only one of the two has an entry in it. Sometimes they both do. Although, they're NEVER both blank. I am trying to create a function that will compare the 2 dates (if there's even 2 there), and find out which one is the earliest of the two. If one date is missing from one column, it will just use the other date as the entry. Once it finds the earliest date between them, I want it to count how many days from the earliest date till today's date. I thought I did this right, but, the results are returning a #VALUE! answer. Can someone see what's going on here and tell me how to make this work? Thanks! =IF(L2="", DAYS360(V2,TODAY())), IF(V2="", DAYS360(L2,TODAY())), IF(L2<V2, DAYS360(L2,TODAY())), IF(L2V2, DAYS360(V2,TODAY())), IF(L2=V2, DAYS360(L2,TODAY())) Do you really need DAYS360? If not, and if L2 or V2 could be after today's date, then =ABS(TODAY()-MIN(L2,V2)) If you need DAYS360, =IF(TODAY()<MIN(L2,V2),DAYS360(TODAY(),MIN(L2,V2)) ,DAYS360(MIN(L2,V2),TODAY( ))) -- To top-post is human, to bottom-post and snip is sublime. |
#9
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
What's Wrong with This?
"D" wrote...
ok- using the ABS version works, kind of. It counts the days, but doesn't return a negative value. ie- the earliest date is already past 86 days ago- 3/24/04. So, I need this to show as a -86 days, instead of a positive 86 days. Is there a work-around for this? Use Bob Phillips's formula, =TODAY()-MIN(L2,V2) but make sure you format the cell as Number with zero decimal places. If you use General format, entering the TODAY function causes (oh so @#$%&*! helpful) Excel to change the cell to a date format, in which case negative values cause the cell to display ########. -- To top-post is human, to bottom-post and snip is sublime. |
#10
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
What's Wrong with This?
"D" wrote...
BTW- how do I know if I need DAYS360 or not? Most the dates will be AFTER today's date, but, eventually will lapse over to being BEFORE todays date, so I need both instances... ... You don't need any function to count the number of days between two dates. Excel stores date values as the number of days from 1-Mar-1900 plus 61. So ABS(OneDate-AnotherDate) is the number of days between two dates ignoring which is earlier and which later. Read online help for DAYS360 to find out if you need to use it, though it seems from your response that you don't need it, so shouldn't use it. -- To top-post is human, to bottom-post and snip is sublime. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help please what am i doing wrong? | Excel Discussion (Misc queries) | |||
Insert Calculated Field (wrong Qty*Price = wrong Amount) | Excel Discussion (Misc queries) | |||
Any wrong with this? | Excel Discussion (Misc queries) | |||
Min Max What's Wrong? | Excel Worksheet Functions | |||
What am I doing wrong? | New Users to Excel |