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
Can you share your sheets please?
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
Google sheets is recognizing your date as Jan 11 instead of November 1
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
Please check column F.
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
You can actually download this sheet that we have. Just go to File > Download As > Microsoft Excel
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
Here's your new formula
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
your row 4
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
[Uploaded an Excel file]
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.
