How to put today's date in a formula

[ka3pmw]

I am using Excel 2007 and windows 7.
I have a sheet that gets its data from another sheet. Both sheets have today's date as part of their file name.
The sheet I am working with Gets its data from another sheet which may or may not be opened.
An example of the data that is put in a cell of the sheet is:

=IF(ISBLANK('C:\Skywarn\[05-04-2018 Emergency Log.xls]FORM'!$B$5),"",'C:\Skywarn\[05-04-2018 Emergency Log.xls]FORM'!$B$5)

This is from a summary sheet which is filled in during the use of the log form. Upon completion of the event, the summary sheet is closed snd sent to various people.

The problem is that I need to update the date, in this case 05-04-2018, automatically.
I hope someone can help.
Thanks for looking at it.

[FDibbins]

=today() will give you today's date, but what you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Sat 05 May 2018) is actually 43225

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

With that said, you will need to use TEXT to convert to the format you need. So maybe something like
=TEXT(TODAY(),"dd-mm-yyyy")

[ka3pmw]

So what you are saying is:

=IF(ISBLANK('C:\Skywarn\[TEXT(TODAY(),"dd-mm-yyyy")  Emergency Log.xls]FORM'!$B$5),"",'C:\Skywarn\[TEXT(TODAY(),"dd-mm-yyyy")  Emergency Log.xls]FORM'!$B$5)

Right?

[FDibbins]

I have a feeling that wont work for you (did you try it yet?) Have not tested that, but I think you will need to include INDIRECT() in there, but that only works on open workbooks

[ka3pmw]

I tried it and no luck. Both workbooks are open. I am not familiar with the INDIRECT().

[FDibbins]

Untested, but something like this...
=IF(ISBLANK(indirect("'C:\Skywarn\"&[TEXT(TODAY(),"dd-mm-yyyy")"&" Emergency Log.xls]FORM'!$B$5")),"",indirect("'C:\Skywarn\"&[TEXT(TODAY(),"dd-mm-yyyy")&" Emergency Log.xls]FORM'!$B$5"))

[ka3pmw]

That didn't work either.

[FDibbins]

I just tested on a sample from my side.
The original formula was this...
='[DV example.xlsx]Sheet1'!$B$4

I put example (with a leading space) in P3, and then used INDIRECT....
=INDIRECT("'[DV"&P63&".xlsx]Sheet1'!$B$4")

You take your original formula, make it text by wrapping the whole reference in ""
Then you remove the part you will be referencing (the date, in your case), and replace with the calc. You need to make sure that you close the "" just before and just after the replaced part, and then you combine it all with &

Try it with just the main formula, remove the ISBLANK part

[ka3pmw]

Ok, let me see if I can explain this better.
I have 3 values that I am getting from my log. They are:
='C:\Skywarn\[05-04-2018 Emergency Log.xls]FORM'!$J$3

='C:\Skywarn\[05-04-2018 Emergency Log.xls]FORM'!$I$3
and
='C:\Skywarn\[05-04-2018 Emergency Log.xls]FORM'!$I$1

I then have a possible maximum of 150 cells of data that get transferred. I can only transfer the data if the cell in the log sheet is NOT blank, hence the ISBLANK.

In all cases, the filename will change based on the date. Yesterday we had a call up so the file names were 05-04-2019 Emergency Log and 05-04-2018 Log Summary.

I have master files called Emergency Log and Log Summary. When I open Emergency Log, it immediately creates mm-dd-yyyy Emergency Log and then opens Log Summary which creates mm-dd-yyy Log Summary. As data is entered in the mm-dd-yyyy Emergency Log, the necessary data is copied to the mm-dd-yyyy Log Summary.If there is a way, I can send you both files.

[FDibbins]

Based on the paths you just showed, what did you adapt the INDIRECT to look like?
If we can get the base formula working OK, we can add the fancy stuff later

[ka3pmw]

I tried the INDIRECT on one of the cells that did not use ISBLANK, ='C:\Skywarn\[05-04-2018 Emergency Log.xls]FORM'!$J$3 and it did not work.

[FDibbins]

Can you show me the INDIRECT formula that you used?

It should look something like this...
=indirect("'C:\Skywarn\["&text(today(),"mm-dd-yyyy&" Emergency Log.xls]FORM'!$J$3")

[ka3pmw]

It is exactly like that and when I execute it, it returns an error on the Log.xls]FORM' part.

Is there a way to do this in VBA? I am no good with VBA but that might be easier than putting formulas in each cell in the Summary.

[FDibbins]

Show me what your formula looks like

[ka3pmw]

=indirect("'C:\Skywarn\["&text(today(),"mm-dd-yyyy&" Emergency Log.xls]FORM'!$J$3")

I've attached the 2 files Let me know if they get there.

[FDibbins]

=indirect("'C:\Skywarn\["&text(today(),"mm-dd-yyyy&" Emergency Log.xls]FORM'!$J$3")


you didnt close the ) on the TEXT............
=indirect("'C:\Skywarn\["&text(today(),"mm-dd-yyyy")&" Emergency Log.xls]FORM'!$J$3")

[ka3pmw]

Ok, that works for the 3 cells that just have a definite value in them. Now what about the cells that I am checking to see if there is any data in?

=IF(ISBLANK('C:\Skywarn\[05-04-2018 Emergency Log.xls]FORM'!$B$4),"",'C:\Skywarn\[05-04-2018 Emergency Log.xls]FORM'!$B$4)

That runs to $B$153

[ka3pmw]

I tried:

=IF(ISBLANK(INDIRECT ("'C:\Skywarn\["&TEXT(TODAY(),"mm-dd-yyyy")&"Emergency Log.xls]FORM'!$B$4)),"", INDIRECT('"C:\Skywarn\["&TEXT(TODAY(),"mm-dd-yyyy")&" Emergency Log.xls]FORM'!$B$4”))

but I get an error at the second '"C: If I can get this to work, I can do an edit on the other 149 lines.

[FDibbins]

The error I see is that for the 2nd part, you have the ' and " swapped around
Maybe this?
=if(isblank(indirect("'C:\Skywarn\["&text(today(),"mm-dd-yyyy&" Emergency Log.xls]FORM'!$J$3"),"",=indirect("'C:\Skywarn\["&text(today(),"mm-dd-yyyy&" Emergency Log.xls]FORM'!$J$3"))

Also, to make copying down easier....
=indirect("'C:\Skywarn\["&text(today(),"mm-dd-yyyy&" Emergency Log.xls]FORM'!J"&rows($1:3))
which then becomes...
=if(isblank(indirect("'C:\Skywarn\["&text(today(),"mm-dd-yyyy&" Emergency Log.xls]FORM'!J"&rows($1:3)) ,"",=indirect("'C:\Skywarn\["&text(today(),"mm-dd-yyyy&" Emergency Log.xls]FORM'!J"&rows($1:3)) )

[ka3pmw]

OK, I made the change, how I missed that I have no idea. I now have another problem. After fixing both occurrences of that I now get an error in the first $B$4.

=if(isblank(indirect('"C:\Skywarn\["&text(today(),"mm-dd-yyyy&" Emergency Log.xls]FORM'!$B$4"),"",=indirect('"C:\Skywarn\["&text(today(),"mm-dd-yyyy&" Emergency Log.xls]FORM'!$B$4"))

What is causing that?

[ka3pmw]

I got the above error cleared. Here is the code:

=IF(ISBLANK(INDIRECT('”C:\Skywarn\["&TEXT(TODAY(),"mm-dd-yyyy")&"Emergency Log.xls]FORM'!$B$4)),"",INDIRECT('"C:\Skywarn\["&TEXT(TODAY(),"mm-dd-yyyy")&" Emergency Log.xls]FORM'!$B$4))

Now I am getting a #REF! error. I opened the Emergency Log first which saved the emergency log with the date in the filename and then opened the Log Summary with the correct date so the cell that was referenced $B$4 should have been found.

[FDibbins]

OK, again I see a possible error in the 1st INDIRECT. It looks like you are using the wrong quotation marks.
=IF(ISBLANK(INDIRECT('”C:\Skywarn\["&TEXT(TODAY(),"mm-dd-yyyy")&"Emergency Log.xls]FORM'!$B$4)),"",INDIRECT('"C:\Skywarn\["&TEXT(TODAY(),"mm-dd-yyyy")&" Emergency Log.xls]FORM'!$B$4))
=IF(ISBLANK(INDIRECT('"C:\Skywarn\["&TEXT(TODAY(),"mm-dd-yyyy")&"Emergency Log.xls]FORM'!$B$4)),"",INDIRECT('"C:\Skywarn\["&TEXT(TODAY(),"mm-dd-yyyy")&" Emergency Log.xls]FORM'!$B$4))

[ka3pmw]

I think your 2 examples are the same. My eyes aren't very good, I need new glasses. It looks like you have a single quote followed by a double quote. Is that correct?

Also, there are 5 double quotes in each of the INDIRECT statements. Why is that?

[FDibbins]

do you see how your quotes slant to the left and mine (and all your others) point down and very slightly to the right?

You are correct about there being 5, I did not notice that, and that too, is a mistake - there should be another 1 at the end of $B$4"

The quotes tell excel it is dealing with text, they need to open, "contain some text", then close
again, see my formula from post #19...
=if(isblank(indirect("'C:\Skywarn\["&text(today(),"mm-dd-yyyy&" Emergency Log.xls]FORM'!$B$3"),"",=indirect("'C:\Skywarn\["&text(today(),"mm-dd-yyyy&" Emergency Log.xls]FORM'!$B$3"))

Not sure where the $J$4 came from, I have reset it to $B$4. Although, because it is text, the $ really are not needed.

[ka3pmw]

Now I am really confused. The formula is supposed to refer to cell B4 in workbook Emergency Log of the current date. It now appears that the formula is just being displayed in the cell as text.

[FDibbins]

Sorry for the confusion, but I will stay on this with you until we figure it out

I have loaded both your files, and because I dont have the same path as you, I removed the C:| part, just add it in where needed.

This worked, and was copy-downable...
C4=IF(INDIRECT("'["&TEXT(TODAY(),"mm-dd-yyyy")&" Emergency Log.xls]FORM'!B"&ROWS($1:4))="","",INDIRECT("'["&TEXT(TODAY(),"mm-dd-yyyy")&" Emergency Log.xls]FORM'!B"&ROWS($1:4)))

[ka3pmw]

I am trying to get the value in the cell B4 on the sheet FORM in workbook Emergency Log onto sheet FORM cell B4 in workbook Log Summary. Why are we using C4= in the example above?

Also if there is nothing in the cell in the Emergency Log, it should be blank.

When I paste the formula in the cell in the log sheet, it is appearing as text not a formula.

If I have to manually edit each line once this is working, that's OK, I don't mind it as long as it gets the data over.

.

[FDibbins]

OK change the FORMAT of that cell to General, I noticed some of your cells are formatted as TEXT.

I have attached your Log file with my formula, copied down. Note that does not have the exact same path as you will need, take a look and see what I did. Play around and let me know how you make out

[ka3pmw]

It appears that all is working well now. Do I have to have the Log Summary file open when I am filling out the Emergency Log?

[FDibbins]

It appears that all is working well now. Do I have to have the Log Summary file open when I am filling out the Emergency Log?

INDIRECT only works on open files, so yes, the file that you are referencing the data from, needs to be open

[ka3pmw]

Where di I mark this solved?

[ka3pmw]

Thanks for hanging in there with me. I am 70 years old and have had 3 strokes. I also have cataracts. I was in the computer industry for 38 years but due to health reasons, I am not the programmer I once was. The fact that we now have this working will make my life much easier when I have to act as net control for an emergency. I am a Ham Radio operator.

[FDibbins]

Thanks for hanging in there with me. I am 70 years old and have had 3 strokes. I also have cataracts. I was in the computer industry for 38 years but due to health reasons, I am not the programmer I once was. The fact that we now have this working will make my life much easier when I have to act as net control for an emergency. I am a Ham Radio operator.

No problem, I understand. I, myself am 60 and had a major heart attack 2 weeks ago, pretty much died in the ER and it took 9 shocks to bring me back.

I am just really happy we got this resolved for you, please dont hesitate to come back again, and thanks for the feedback

[ka3pmw]

Where do I mark this SOLVED?

It's been a long time since I was up in your part of the state.

[FDibbins]

I have done it for you, it's under the Thread Tools, towards the top of the page.

The 2 files came through OK, did you need me to check them again for you?
(I see there is again a reference to J3 in there?)

[ka3pmw]

I only sent them once. They must be the original files. Thanks again.

[FDibbins]

No problem - again, Im happy to help

[ka3pmw]

Glad you did. Thanks again.

[ka3pmw]

Found a major problem with this. If I use anything in the Summary form that references the log form I get #REF! in the cells. I need to put the instructions to fill in the Summary form in the Log form. That way the summary is an independent standalone form. I have attached the files here. Specifically I need to copy from:
Log -> Summary
I1 -> I1
I3 -> I3
J3 -> J3
B4:B153 -> B4:B153
I have a line in the VBA that converts everything to uppercase, maybe it could be done there.
The password for the sheet and the VBA is ka3pmw

[FDibbins]

1st, as I said, any file that is referenced using INDIRECT (source file) needs to be open, otherwise the "result file" will show an error.
I just opened both your files and MOST of the errors disappeared.

the few that were left appear to be referenice a 3rd file?
=INDIRECT("'C:\Skywarn\["&TEXT(TODAY(),"mm-dd-yyyy")&" Emergency Log.xls]FORM'!$i$3")

[ka3pmw]

Ok, I fixed that problem since I sent those files. By the way the files are located in the C:\Skywarn\ folder so that may be the problem. Ti you look at the summary file you will see formulas in there that I believe are causing the problem. My goal is to remove those and have the log file update the summary file. This would make the summary a file with no formulas or references in it. It would also eliminate the INDIRECT statements all together.