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]

maybe this is a start

=IF(C2>B2,"-"&C2-B2,B2-C2)

 

[TommyLee]

Hi mole999,

thank you for your answer, but anyway I do not think so, that this would be working. Because there is a also problem with format of days, I am not able to transfer the format to a number, because of crazy format, could be more easy maybe.

Thanks.

Regards,
TL

 

[jorismoerings]

or this:

Excel 2016 (Windows) 64 bit
	A	B	C	D	E
1	27-9-2016	26-9-2016		1	1
2	30-9-2016	7-10-2016	14-10-2016 08:41	-7	-11
3	30-9-2016	7-10-2016 08:41		-7	-7
Sheet1

Cell Formulas
Range	Formula
D3	=INT(A3)-INT(B3)
E3	=IF(ISBLANK(C3),INT(A3)-INT(B3),INT(A3)-INT(C3)+(INT(A3)-INT($A$1)))

 

[TommyLee]

Hi jorismorings,

thank you for your answer, but the problem is to convert this format to something better:

16/10/2016 23:59:26, nothing has been working for me yet and i tried a lot, so i am really stuck

do you have pls any idea how to convert this f...ing format to workable format?

thank you

regards,
tl

 

[jorismoerings]

TommyLee,

Your question left to much assuming to provide you with an answer. To help you need to define what's wrong with the current format and what's better!

From your example i assumed the dates mentioned were formatted date / time serialnumbers. If that's true you don't need worrying about the format because a format is just that. Excel isn';t using the format of the field but is using the underlying date/time serialnumber for it's calculation.

If the dates in your example are in a textfield we can use the DATEVALUE function to convert the test to usable Date/Time serialnumber; the formula in E3 would look something like this:
=IF(ISBLANK(C3),DATEVALUE(A3)-DATEVALUE(B3),DATEVALUE(A3)-DATEVALUE(C3)+(DATEVALUE(A3)-DATEVALUE($A$1)))

Hope this helps

 

[FDibbins]

What exactly do your date cells contain?
Test with - in another empty cell somewhere - =isnumber(cell-with-date)...if you get FALSE, then that is not really a date, it is text and will need to be converted to a date 1st

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F
1				Col A - Col B	Col A - Col C or D if C is Blank	Add 3
2	27-09-16	26-09-16		1	1	1
3	30-09-16	07-10-16	14-10-16 8:41	-7	-14	-17
4	30-09-16	07-10-16 8:41		-7	-7	-7
5	30-09-16	07-10-16 8:41	16-10-16 23:59	-7	-16	-19
6	or
7	30-09-16	07-10-16 8:41	16-10-16 23:59	-7	-19
8	30-09-16	07-10-16 8:41	16-10-2016 11:59:26 PM	-7	-19
9
1e

Cell Formulas
Range	Formula
D2	=A2-INT(B2)
D3	=A3-INT(B3)
D4	=A4-INT(B4)
D5	=A5-INT(B5)
D7	=A7-INT(B7)
D8	=A8-INT(B8)
E2	=IF(ISBLANK(C2),D2,A2-INT(C2))
E3	=IF(ISBLANK(C3),D3,A3-INT(C3))
E4	=IF(ISBLANK(C4),D4,A4-INT(C4))
E5	=IF(ISBLANK(C5),D5,A5-INT(C5))
E7	=IF(ISBLANK(C7),D7,A7-INT(C7)-(C7>B7)*3)
E8	=IF(ISBLANK(C8),D8,A8-INT(C8)-(C8>B8)*3)
F2	=IF(C2>B2,E2+3,E2)
F3	=IF(C3>B3,E3-3,E3)
F4	=IF(C4>B4,E4-3,E4)
F5	=IF(C5>B5,E5-3,E5)

You stated "but + 3 days". If that means additional 3 days to the negative see above.
Please edit as required and/or provide additional information and a concise example.
C8 is text. The calculations coerce the text to numbers.

 

[TommyLee]

What exactly do your date cells contain?
Test with - in another empty cell somewhere - =isnumber(cell-with-date)...if you get FALSE, then that is not really a date, it is text and will need to be converted to a date 1st

Hi FDibbins, thank you for answer.

I tested the format and seems that some of the dates are number and some of the dates are not.

It is crazy extract.

Do you have idea please, what to do with this?

Thank you.
TL

 

[TommyLee]

Excel 2010

	A	B	C	D	E	F
1				Col A - Col B	Col A - Col C or D if C is Blank	Add 3
2	27-09-16	26-09-16		1	1	1
3	30-09-16	07-10-16	14-10-16 8:41	-7	-14	-17
4	30-09-16	07-10-16 8:41		-7	-7	-7
5	30-09-16	07-10-16 8:41	16-10-16 23:59	-7	-16	-19
6	or
7	30-09-16	07-10-16 8:41	16-10-16 23:59	-7	-19
8	30-09-16	07-10-16 8:41	16-10-2016 11:59:26 PM	-7	-19
9

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

1e​

Worksheet Formulas Cell Formula D2 =A2-INT(B2) E2 =IF(ISBLANK(C2),D2,A2-INT(C2)) F2 =IF(C2>B2,E2+3,E2) D3 =A3-INT(B3) E3 =IF(ISBLANK(C3),D3,A3-INT(C3)) F3 =IF(C3>B3,E3-3,E3) D4 =A4-INT(B4) E4 =IF(ISBLANK(C4),D4,A4-INT(C4)) F4 =IF(C4>B4,E4-3,E4) D5 =A5-INT(B5) E5 =IF(ISBLANK(C5),D5,A5-INT(C5)) F5 =IF(C5>B5,E5-3,E5) D7 =A7-INT(B7) E7 =IF(ISBLANK(C7),D7,A7-INT(C7)-(C7>B7)*3) D8 =A8-INT(B8) E8 =IF(ISBLANK(C8),D8,A8-INT(C8)-(C8>B8)*3) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

You stated "but + 3 days". If that means additional 3 days to the negative see above.
Please edit as required and/or provide additional information and a concise example.
C8 is text. The calculations coerce the text to numbers.

Hi David,

thank you for your answer.

Seems very helpful, but unfortunately i cannot still do a proper test, because of the format. The extract is totally wrong and some of the values are numbers and some of the values are not.

So i am just wondering how to convert these data with are extracted on daily basis to number format.

If you have pls any idea, i would really appreciate any help.

Thank you.

Regards,
TL