difference between dates with if function

[TommyLee]

Hi folks,

please, could you help me to solve following issue?

The problem is, that in first column I have for example today's date. In the second I could have different date, could be future date or past date. Column number 3 could be empty, could be with future date or could be with 01/01/0001 00:00:00 format. The fourth column should be difference between first column and the second column. But unfortunately, this is wrong in case that in third column is a different date of the second column. For example the second row: today's date is 30 of September, second column is the seventh of October and the third column is 14th of October. If the date is later than in the first column and later than the second column, the result should be something as difference between the first column and the third column + 3, but result should be in negative value in this case, because there are 14 days to go but + 3 days, so - 11 eleven days to go. In case that the third column is empty or there is a value as 01/01/0001 00:00:00, the result should be simple difference between the first column and the second column. The time is not important.

Please, I really appreciate any help, how to solve this issue.

Thank you understanding and also for any help.

Regards,
TL

today date for example 27/09/2016 <tbody> </tbody><colgroup><col></colgroup>	26/09/2016 08:41:22 <tbody> </tbody><colgroup><col></colgroup>	<tbody> </tbody><colgroup><col></colgroup>	1 <tbody> </tbody><colgroup><col></colgroup>	1 <tbody> </tbody><colgroup><col></colgroup> <tbody> </tbody><colgroup><col></colgroup>
30/09/2016	07/10/2016 08:41:22	14/10/2016 08:41:22	-7	-11
30/09/2016	07/10/2016 08:41:22	01/01/0001 00:00:00 <tbody> </tbody><colgroup><col></colgroup>	-7	-7

<tbody>
</tbody>

 

[mole999]

add a double minus sign to the formuals, that should force a text number to a number

 

[Dave Patton]

Hello TommyLee

If you post a concise example of your data and the expected result, we may be able to provide assistance.

 

[TommyLee]

add a double minus sign to the formuals, that should force a text number to a number

Hi Mole,

thank you for your help, this case is solved.

I use the text to column to transfer text to number and it works.

But anyway i was wondering about your advice. If I understand it correctly, should i use the formula as =--(A2-B2) for example? Because i tried it and unfortunately, i did not work. But would be great to find out, what is going on, because this would be very useful.

Thank you.

Regards,
TL

 

[TommyLee]

Hello TommyLee

If you post a concise example of your data and the expected result, we may be able to provide assistance.

Hi Dave,

thank you for your help.

It is solved finally. I really appreciate your help. This was the first time for me, when i should really work with dates in text format, so new experience for my. Anyway, have to use text to column to transform date in text format to workable format. Mole advised me to try double minus sign before formula, but actually does not work to me. Do you have any idea how can i do the transformation faster, because i have to do every day. With Mole advise it would be great, but...

Thank you.

Regards,
TL

 

[mole999]

the double minus tries to perform a numerical function, minus minus is plus, so becomes treated as a number. I use it infrequently. a plain worksheet will left align text and right align numbers, so before making it look pretty get it practical

 

[TommyLee]

the double minus tries to perform a numerical function, minus minus is plus, so becomes treated as a number. I use it infrequently. a plain worksheet will left align text and right align numbers, so before making it look pretty get it practical

Hi Mole,

unfortunately, it really does not work for me, i can only see the VALUE! error.

Do you have idea what is going on, because it would be really helpful for me.

Regards,
TL

 

[Dave Patton]

As I advised in post #8,

please post a concise example of your information and indicate the expected results.


What are the regional settings on your Computer for your location?

Download one of the tools such as

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2545970

and use the tool to post a small example to this thread.

 

[TommyLee]

As I advised in post #8,

please post a concise example of your information and indicate the expected results.


What are the regional settings on your Computer for your location?

Download one of the tools such as

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2545970

and use the tool to post a small example to this thread.

Hi Dave,

thank you. I have already downloaded of the tool.

Really appreciate.

I will try to test the result what i have and if there would be any problem, i will ask kindly for help.

Again, I really appreciate your help.

Regards,
TL