difference between dates with if function

T

TommyLee

New Member
Joined
Aug 31, 2016
Messages
23
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
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 134"]
<tbody>[TR]
[TD]today date for example

27/09/2016[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 149"]
<tbody>[TR]
[TD]26/09/2016 08:41:22[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 134"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="align: right"]1[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="align: right"]1[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]30/09/2016 [/TD]
[TD]07/10/2016 08:41:22[/TD]
[TD]14/10/2016 08:41:22[/TD]
[TD]-7[/TD]
[TD]-11[/TD]
[/TR]
[TR]
[TD]30/09/2016 [/TD]
[TD]07/10/2016 08:41:22[/TD]
[TD][TABLE="width: 134"]
<tbody>[TR]
[TD]01/01/0001 00:00:00[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]-7[/TD]
[TD]-7[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
maybe this is a start

=IF(C2>B2,"-"&C2-B2,B2-C2)
 
Upvote 0
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
 
Upvote 0
or this:


Excel 2016 (Windows) 64 bit
ABCDE
127-9-201626-9-201611
230-9-20167-10-201614-10-2016 08:41-7-11
330-9-20167-10-2016 08:41-7-7
Sheet1
Cell Formulas
RangeFormula
D3=INT(A3)-INT(B3)
E3=IF(ISBLANK(C3),INT(A3)-INT(B3),INT(A3)-INT(C3)+(INT(A3)-INT($A$1)))
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Last edited:
Upvote 0
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
 
Upvote 0

Excel 2010
ABCDEF
1Col A - Col BCol A - Col C or D if C is BlankAdd 3
227-09-1626-09-16111
330-09-1607-10-1614-10-16 8:41-7-14-17
430-09-1607-10-16 8:41-7-7-7
530-09-1607-10-16 8:4116-10-16 23:59-7-16-19
6or
730-09-1607-10-16 8:4116-10-16 23:59-7-19
830-09-1607-10-16 8:4116-10-2016 11:59:26 PM-7-19
9
1e
Cell Formulas
RangeFormula
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.
 
Last edited:
Upvote 0
FDibbins said:
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
Click to expand...

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
 
Upvote 0
Dave Patton said:
Excel 2010
ABCDEF
Col A - Col BCol A - Col C or D if C is BlankAdd 3
or
16-10-2016 11:59:26 PM

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]27-09-16[/TD]
[TD="bgcolor: #FFFFFF, align: right"]26-09-16[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]30-09-16[/TD]
[TD="bgcolor: #FFFFFF, align: right"]07-10-16[/TD]
[TD="bgcolor: #FFFFFF, align: right"]14-10-16 8:41[/TD]
[TD="bgcolor: #FFFFFF, align: right"]-7[/TD]
[TD="bgcolor: #FFFFFF, align: right"]-14[/TD]
[TD="align: right"]-17[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]30-09-16[/TD]
[TD="bgcolor: #FFFFFF, align: right"]07-10-16 8:41[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]-7[/TD]
[TD="bgcolor: #FFFFFF, align: right"]-7[/TD]
[TD="align: right"]-7[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF, align: right"]30-09-16[/TD]
[TD="bgcolor: #FFFFFF, align: right"]07-10-16 8:41[/TD]
[TD="align: right"]16-10-16 23:59[/TD]
[TD="bgcolor: #FFFFFF, align: right"]-7[/TD]
[TD="bgcolor: #FFFFFF, align: right"]-16[/TD]
[TD="align: right"]-19[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFFFF, align: right"]30-09-16[/TD]
[TD="bgcolor: #FFFFFF, align: right"]07-10-16 8:41[/TD]
[TD="align: right"]16-10-16 23:59[/TD]
[TD="bgcolor: #FFFFFF, align: right"]-7[/TD]
[TD="bgcolor: #FFFFFF, align: right"]-19[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]30-09-16[/TD]
[TD="bgcolor: #FFFFFF, align: right"]07-10-16 8:41[/TD]

[TD="bgcolor: #FFFFFF, align: right"]-7[/TD]
[TD="bgcolor: #FFFFFF, align: right"]-19[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
1e​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=A2-INT(B2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=IF(ISBLANK(C2),D2,A2-INT(C2))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=IF(C2>B2,E2+3,E2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]=A3-INT(B3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"]=IF(ISBLANK(C3),D3,A3-INT(C3))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F3[/TH]
[TD="align: left"]=IF(C3>B3,E3-3,E3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]=A4-INT(B4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E4[/TH]
[TD="align: left"]=IF(ISBLANK(C4),D4,A4-INT(C4))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F4[/TH]
[TD="align: left"]=IF(C4>B4,E4-3,E4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D5[/TH]
[TD="align: left"]=A5-INT(B5)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E5[/TH]
[TD="align: left"]=IF(ISBLANK(C5),D5,A5-INT(C5))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F5[/TH]
[TD="align: left"]=IF(C5>B5,E5-3,E5)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D7[/TH]
[TD="align: left"]=A7-INT(B7)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E7[/TH]
[TD="align: left"]=IF(ISBLANK(C7),D7,A7-INT(C7)-(C7>B7)*3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D8[/TH]
[TD="align: left"]=A8-INT(B8)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E8[/TH]
[TD="align: left"]=IF(ISBLANK(C8),D8,A8-INT(C8)-(C8>B8)*3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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.
Click to expand...

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
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
247
dshtesy
D
A
  • Question Question
Need to convert days, hours, minutes and second text into hh:mm:ss
Replies
7
Views
857
Aadya
A
O
  • Solved
Travel Duration allowing for more than one day and different time zones
Replies
7
Views
142
myall_blues
myall_blues
B
  • Question Question
Excel Pivot Table to see the number of "Lost Calls"
Replies
0
Views
57
Bernie Galve
B
Jyggalag
  • Solved
Formula to calculate between dates
Replies
4
Views
619
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top