Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

a date formula that results in showing the date as 29/12/00 (date format as dd/mm/yy) - use column BA to calculate 364 days (same date format) in column BS, if BA blank, then calculate 364 days using column AX. Results for column BA are correct. However, if column BA is empty, then it should calculate using column AX, which has data, but the result is always 29/12/00, regardless of the date in column AX. I have used this formula with success in another workbook, but this file wont work! Formula: =IF(AND(LEN(\$BA74)=0,LEN(\$AX74)=0),"",IF(LEN(\$BA74)=0,DATE(YEAR(\$AX74),MONTH(\$AX74),DAY(\$AX74)+364),DATE(YEAR(\$BA74),MONTH(\$BA74),DAY(\$BA74)+364)))
Solved by A. L. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 09/08/2018 - 07:21
Greetings!
Excelchat Expert 09/08/2018 - 07:22
Thank you for choosing Got It Pro!
Excelchat Expert 09/08/2018 - 07:22
User 09/08/2018 - 07:22
how do i share my sheet?
Excelchat Expert 09/08/2018 - 07:22
You can send it by clicking on the clip icon beside the message window
Excelchat Expert 09/08/2018 - 07:22
Then press enter.
User 09/08/2018 - 07:23
i will need to extract it, one moment, its too big!
Excelchat Expert 09/08/2018 - 07:23
Okay, please just use minimal data.
Excelchat Expert 09/08/2018 - 07:24
A few lines should be fine so that we could see what we're dealing with. :)
User 09/08/2018 - 07:25
sorry, its very slow!
Excelchat Expert 09/08/2018 - 07:25
No worries.
User 09/08/2018 - 07:26
can I copy & paste instead? Excel is hanging!
Excelchat Expert 09/08/2018 - 07:26
Sure
Excelchat Expert 09/08/2018 - 07:26
Paste it here on our shared worksheet
User 09/08/2018 - 07:27
sorry, excel egg timing.....
Excelchat Expert 09/08/2018 - 07:28
No worries. :)
User 09/08/2018 - 07:29
ok, will paste, in 2 separate sections
Excelchat Expert 09/08/2018 - 07:30
Sure.
User 09/08/2018 - 07:31
Planned Implementation Date Actual Implementation Date 1/11/18 1/1/18 1/1/18 1/10/18 1/9/17 1/9/17 1/10/18 1/1/18 1/1/18 can you see teh formula though?
Excelchat Expert 09/08/2018 - 07:31
Nope, no formula.
User 09/08/2018 - 07:32
now?
Excelchat Expert 09/08/2018 - 07:32
Still nothing, all of these were pasted as texts.
User 09/08/2018 - 07:33
B10 i can see the formula in the formula bar: =IF(AND(LEN(\$BA36)=0,LEN(\$AX36)=0),"",IF(LEN(\$BA36)=0,DATE(YEAR(\$AX36),MONTH(\$AX36),DAY(\$AX36)+364),DATE(YEAR(\$BA36),MONTH(\$BA36),DAY(\$BA36)+364)))
Excelchat Expert 09/08/2018 - 07:33
Oh okay, it's in B10. Yeap. I see it
User 09/08/2018 - 07:33
Planned date is AX, Actual date is BA
Excelchat Expert 09/08/2018 - 07:33
Can you quickly explain the requirement step by step? Each condition
User 09/08/2018 - 07:34
if Actual date is empty, use Planned date, calculate both for 1 year/12 months in the future, into your column C
User 09/08/2018 - 07:34
Actual date calulates ok, Planned gives 29/12/00
Excelchat Expert 09/08/2018 - 07:35
If Column B is empty, use column A to calculate. So you want to add 1 year to that date?
User 09/08/2018 - 07:35
both should end in the result of calculating how many months in the year, (jan to Dec)
Excelchat Expert 09/08/2018 - 07:35
And put it in column C?
User 09/08/2018 - 07:35
yes
User 09/08/2018 - 07:35
yes
Excelchat Expert 09/08/2018 - 07:35
Okay.
Excelchat Expert 09/08/2018 - 07:36
Give me a few minutes please.
User 09/08/2018 - 07:36
this formula works fine in another workbook, but not in this one, everything, date format wise, is the same
User 09/08/2018 - 07:36
thanks
Excelchat Expert 09/08/2018 - 07:36
01/11/2018 is dd/mm/yyyy?
User 09/08/2018 - 07:37
yes
Excelchat Expert 09/08/2018 - 07:37
Okay
User 09/08/2018 - 07:41
NB i am using Excel 2010
Excelchat Expert 09/08/2018 - 07:41
Noted.
Excelchat Expert 09/08/2018 - 07:42
User 09/08/2018 - 07:42
ha ha, really! so maybe is a date format issue?
Excelchat Expert 09/08/2018 - 07:43
Yes, most likely.
User 09/08/2018 - 07:43
teh end date should calculate to 31 Oct 19
User 09/08/2018 - 07:43
for row 2
Excelchat Expert 09/08/2018 - 07:44
Oh, you want the date to be added 364 days, right?
User 09/08/2018 - 07:44
yes
Excelchat Expert 09/08/2018 - 07:44
Not really move 2018 to 2019 for example?
Excelchat Expert 09/08/2018 - 07:44
Okay
User 09/08/2018 - 07:45
no, dates should start 1st of the month, but end last day of month
Excelchat Expert 09/08/2018 - 07:46
User 09/08/2018 - 07:47
so it still only works correctly in the Actual implementation date, the Planned date seems to end 1 month short
Excelchat Expert 09/08/2018 - 07:47
But the planned and actual date are both the same
User 09/08/2018 - 07:47
is that 1st of Oct or 10 Jan?
Excelchat Expert 09/08/2018 - 07:47
Check Row 3.
Excelchat Expert 09/08/2018 - 07:48
It used 3/1 instead of the 1/1
Excelchat Expert 09/08/2018 - 07:48
That would be 10th of Jan
Excelchat Expert 09/08/2018 - 07:48
We should probably make them all the same format. haha
User 09/08/2018 - 07:48
yes
Excelchat Expert 09/08/2018 - 07:48
There.
User 09/08/2018 - 07:49
no, not right, the month and day have now been transposed
User 09/08/2018 - 07:49
yes
Excelchat Expert 09/08/2018 - 07:49
What do you mean transposed?
User 09/08/2018 - 07:50
dont worry, you have corrected it (was back to front)
Excelchat Expert 09/08/2018 - 07:50
Okay.
User 09/08/2018 - 07:50
can you make column F a bit wider please?
Excelchat Expert 09/08/2018 - 07:50
There you go.
User 09/08/2018 - 07:51
actually, Planned date still transposed, 3rd row should be 1 oct
Excelchat Expert 09/08/2018 - 07:52
It is a date format issue
Excelchat Expert 09/08/2018 - 07:52
All correct now, right?
User 09/08/2018 - 07:52
yes, good
Excelchat Expert 09/08/2018 - 07:53
Okay, so use the formula in the F column instead of the current one you're using.
Excelchat Expert 09/08/2018 - 07:53
=IF(B2="",DATE(YEAR(A2),MONTH(A2),DAY(A2))+364,DATE(YEAR(B2),MONTH(B2),DAY(B2)+364))
User 09/08/2018 - 07:53
F5 not correct
Excelchat Expert 09/08/2018 - 07:53
Ah because the actual date.
Excelchat Expert 09/08/2018 - 07:53
There you go
User 09/08/2018 - 07:53
ok
Excelchat Expert 09/08/2018 - 07:54
Alright. Are we good?
User 09/08/2018 - 07:54
so the formula you use is above at 08:53?
Excelchat Expert 09/08/2018 - 07:54
Yeap.
Excelchat Expert 09/08/2018 - 07:55
Here it is again.
Excelchat Expert 09/08/2018 - 07:55
=IF(B2="",DATE(YEAR(A2),MONTH(A2),DAY(A2))+364,DATE(YEAR(B2),MONTH(B2),DAY(B2)+364))
Excelchat Expert 09/08/2018 - 07:55
Excelchat Expert 09/08/2018 - 07:55
You'll also have a transcript of this session sent to your email, so you'd be able to review it.
User 09/08/2018 - 07:55
ok, so will that also cope if both columns are empty
Excelchat Expert 09/08/2018 - 07:56
No, you only gave two conditions. If acutal is empty, use the planned date
User 09/08/2018 - 07:56
so if both empty, will it show as an error?
Excelchat Expert 09/08/2018 - 07:56
If both is empty, nothing will show.
Excelchat Expert 09/08/2018 - 07:56
Do you want to show an error?
User 09/08/2018 - 07:56
no
User 09/08/2018 - 07:57
just blank
Excelchat Expert 09/08/2018 - 07:57
Okay, hold on.
User 09/08/2018 - 07:57
:-)
Excelchat Expert 09/08/2018 - 08:00
Excelchat Expert 09/08/2018 - 08:00
=IF(AND(A2="",B2="")," ",IF(B2="",DATE(YEAR(A2),MONTH(A2),DAY(A2))+364,DATE(YEAR(B2),MONTH(B2),DAY(B2)+364)))
Excelchat Expert 09/08/2018 - 08:00
First row as an example.
User 09/08/2018 - 08:01
ok, seems to work, do you mind if i test this in my workbook?
Excelchat Expert 09/08/2018 - 08:02
Go ahead. Make sure you point to the correct columns.
User 09/08/2018 - 08:04
so i used this in row 61, BA is Actual date and AX is Planned date, but the results are the same as before 29/12/00 - i like your formula, much simpler than mine, but maybe this is formatting? although i cannot see how
User 09/08/2018 - 08:04
=IF(AND(AX61="",BA61="")," ",IF(BA61="",DATE(YEAR(AX61),MONTH(AX61),DAY(AX61))+364,DATE(YEAR(BA61),MONTH(BA61),DAY(BA61)+364)))
Excelchat Expert 09/08/2018 - 08:04
What is your date in AX61?
User 09/08/2018 - 08:04
1/9/17
Excelchat Expert 09/08/2018 - 08:05
Which is Sept1?
User 09/08/2018 - 08:05
sorry, looking at wrong row
Excelchat Expert 09/08/2018 - 08:05
That's fine.
User 09/08/2018 - 08:05
1/10/18 - October
Excelchat Expert 09/08/2018 - 08:05
Okay, what is in BA61?
User 09/08/2018 - 08:05
blank
Excelchat Expert 09/08/2018 - 08:06
Okay
User 09/08/2018 - 08:06
Excelchat Expert 09/08/2018 - 08:06
Can you try to reenter the date in AX61? Enter it as 10/1 and then press enter
User 09/08/2018 - 08:06
ok
Excelchat Expert 09/08/2018 - 08:06
Excel recognizes dates as mm/dd/yyyy everytime
User 09/08/2018 - 08:07
no difference
Excelchat Expert 09/08/2018 - 08:07
What is showing now in AX61?
User 09/08/2018 - 08:08
29/12/00
Excelchat Expert 09/08/2018 - 08:09
Change AX61
Excelchat Expert 09/08/2018 - 08:09
Make it October 1 or 10/1
User 09/08/2018 - 08:11
tried that, also tried changeing date format to 1 October 2018, still same result, let me try changing format in the End date column....
User 09/08/2018 - 08:11
no, 29 December 1900
Excelchat Expert 09/08/2018 - 08:14
Can you send me a screenshot?
User 09/08/2018 - 08:14
one momen
User 09/08/2018 - 08:15
can i paste into your workbook?
Excelchat Expert 09/08/2018 - 08:15
Sure.
Excelchat Expert 09/08/2018 - 08:15
Or you can send as well using the clip icon
User 09/08/2018 - 08:16
User 09/08/2018 - 08:17
did that work?
Excelchat Expert 09/08/2018 - 08:17
yes
Excelchat Expert 09/08/2018 - 08:17
the formula is in the 29 december 1900
User 09/08/2018 - 08:18
yes

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.