Hi all,
The date and time of closure of a project is logged in the format "dd/mm/yy hh:mm:ss", and I want only the first part to show in my report.
I have tried several options but to no avail.
Does anybody have a suggestion how to cut off the time from this cell value?
Thanks,
Willem
Date
Votes
6 comments
-
Jet Reports Historic Posts Official comment Okay - it appears that your date is returning as a true date (at least if you are getting a serial number using the first method instead of anything recognizable) - as noted at the top of my earlier explanation, I was assuming it was returning as text and therefore not easily changed. :)
So, knowing this - try the method below (still using a helper column)
since your data is clearly coming out of your database as a date - we just need to tell Excel to show only the portion of the date you care about:
=NP("EVAL","=TEXT("""&K22&""",""MM/DD/YY"")")
Note that I'm still assuming that changing the cell format to 'short date' isn't an option here. Good luck! -
Jet Reports Historic Posts I'm going to assume that the output is not a true 'date' according to Excel and that therefore you can't just reformat the cell. (which would be your easiest method)
So, if you can't just change the way Excel displays the data, you'll need to take the longer route :)
I'm also going to assume that the output is exactly what you've shown, with a 2 digit code for the year, and following a / symbol.
If you always get 2 digits for the day and month - so that 01/01/14 shows for January first, you can wrap your Jet formula in this:
=LEFT(JET FORMULA,8)
for this one - we were basically telling excel that you only want the first 8 characters of the output.
If you don't (so January 1 shows as 1/1/14), then it's a bit longer, but still doable:
for this one, you'll need a helper cell - and we'll put that into an NP(EVAL statement so that you can have the final output stand alone and not require the Jet formula if you want to delete or hide that column
Assuming that your Jet formula is in Cell K22 here:
=NP("EVAL","=LEFT("""&K22&""",FIND(""/"","""&K22&""",4)+2)")
The formula without the EVAL complication is this: =LEFT(K22,FIND("/",K22,4)+2)
Here we are telling Excel that you need to find the location of the 2nd / symbol (that's the FIND part of the formula), and add two characters (for the years) behind that. and return that number of characters beginning from the left of the output.
NOTE: The """& &""" characters are needed due to the EVAL Jet formula, they let this formula be copied down by a rows command and the formula reference will update to the appropriate row. -
Jet Reports Historic Posts :| Hi Heather,
I want to thank you for your elaborate answer, and I had high hopes…… it looked like it could work.
So I tried both options, but the first one just returns the serial number of the date, formatted as text. I see no way to reformat the cell to get the date again.
The second option (both with and without the =EVAL) return a #VALUE error; probably because the FIND statement doesn't work with dates. So I'm still stuck on this one.
I'm working with Jet Essentials 2012 on a MS Dynamics NAV 5.0 SP@ database.
I hope that something else pops up that can help me……
Rgds
Willem -
Jet Reports Historic Posts Hi willem,
if Heathers suggestion is not working for you, maybe you can attache an example to your post, so we can try….
regards
Jetsetter -
Jet Reports Historic Posts Heather,
That seems to work :lol: ! Sorry that it took a few days, but we had some holidays around Easter, and I was away on a training so I was not able to respond.
I knew it was something that needed solving in Excel, but could not find the right solution. :oops:
This is another snippet to be saved.
I have formatted the cell as date, but it is filled with text; sorting and filtering may give some problems, but we will find a way to get around that.
Thanks again,
Willem -
Jet Reports Historic Posts glad that the second solution worked Willem - if the sorting and filtering become too much of an issue, perhaps uploading an example of your report (and output if possible), would give someone the chance to help fine-tune this. :)
~Heather
Please sign in to leave a comment.