Network days

Solved/Closed
Jay - Feb 11, 2010 at 12:07 PM
 jay - Feb 11, 2010 at 01:04 PM
Hello,
I am trying to run the network formula days using the below formate as teh date, clearly this doesnt work anyway I can adapt this suit or anyway of reformatting it?? Note; this specific cell i am pasting in from a report sent to me and the date and time stamp are present in one cell, it would be too time consuming for me to re insert each date with the correct formate. Just wondering if there was an alternative way around this??/
Any help is appreciated thanks;

This is the format of the cell currently;

Feb 5 2010 6:42PM

3 replies

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Feb 11, 2010 at 12:25 PM
Well how about create a user defined function and use CDATE to return the date that you can use as you would like


1. Press ALT + F11
2. Click on Insert and choose Module
3. Paste

Public Function showDate(inDate As String) As Date

'if is a date, return value as month / day / year
If IsDate(inDate) Then showDate = Format(CDate(inDate), "mm/dd/yyyy")

End Function

Then you can use this function on you sheet. Lets say your date is in cell A1, then in B1 you can type

=showDate(a1)
you can format cell to what ever way you want.
0
Thanks!

sadly, it sis not working It simply return me back to the visual basic sheet and highlights the error in (inDate).

Just to confirm i typed in the below within the VB;

'if is a date, return value as month / day / year
If IsDate(inDate) Then showDate = Format(CDate(inDate), "mm/dd/yyyy")

End Function.

The below is is a shot of what i see in my spreadie;

LastAuditDateTime
Feb 5 2010 6:42PM
Feb 5 2010 6:41PM
Feb 4 2010 3:47PM
Feb 4 2010 1:33PM
Feb 5 2010 6:44PM
Aug 7 2009 5:57PM
Feb 8 2010 6:26PM
Feb 5 2010 2:36PM
Dec 23 2009 1:51PM
Jan 22 2010 1:27PM
Feb 5 2010 2:35PM
Feb 4 2010 3:47PM
Feb 4 2010 3:46PM
Feb 4 2010 3:47PM
Oct 27 2006 6:02PM

This info is all in once cell and i just need to run a network days formula of this, put it wont recognise the time etc which is causing the problem and im unsure how to reformat the cells to a recognisable format??
0
Sorry im being a numpty your formula works but the following occurs when i run it for example the following date;

Feb 5 2010 6:42PM

for the above i get 02-May-10??? not sure why??? any ideas, please?

help MUCH appreciated.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Feb 11, 2010 at 12:47 PM
You did type this line too right ?


Public Function showDate(inDate As String) As Date


If it still does not work, put your file at https://authentification.site
0
Yes inserted the whole formula but i get '02-May-10' as teh result when actual cell represents
Feb 5 2010 6:42PM. The month is being picked up by the number '5' of the actual cell and the 2 from the begining of the year, i guess??
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Feb 11, 2010 at 12:56 PM
Ah well it is coz of regional issues. Change this ""mm/dd/yyyy" to dd/mm/yyyy
0
Done the trick, 1 word for you mate, LEGEND. Cheersssss
0