Possible Daft question

[MarkAndrews]

I have a long list of data, formatted Date (dd/mm/yy) & time

Whats the best way to get rid of the time (Text to columns?) & still keep the date?

I need to perform networkdays on the date with another column

Sample below

TIA

Abort Data.xls
	F	G	H	I
4	2/8/0612:13
Jan

 

[SteveO59L]

Just format your entries as dates ?

Dont think it would make any difference to networkdays anyway

 

[MarkAndrews]

I've tried it mate, & it wont have it?

 

[MarkAndrews]

Sample of updated data

Please ignore Columns E & G (These are the columns created when text to columns was done)

Abort Data.xls
	A	B	C	D	E	F	G	H	I	J
1	ClientID	IntroducerID	MatterID	StartDate		AbortDate		Networkdays	AuthActDate	AuthactReceived
2	10	40	563785	03/01/06	00/01/00	NULL		#VALUE!	NULL	No
3	10	40	563787	03/01/06	00/01/00	NULL		#VALUE!	NULL	No
Jan

 

[barry houdini]

Networkdays gives you an error because F2 doesn't contain a date.

...perhaps you need something like

=IF(F2="Null","",NETWORKDAYS(D2,F2))

 

[Richard Schollar]

Hi Mark

BNot sure this is the best way but you should be able to use (to get rid of the time this is):

=DATEVALUE(TEXT(D2,"ddmmmyyyy"))

Best regards

Richard

PS Or just

=INT(D2)

of course...

 

[MarkAndrews]

Networkdays gives you an error because F2 doesn't contain a date.

...perhaps you need something like

=IF(F2="Null","",NETWORKDAYS(D2,F2))

Thanks Barry

& thanks Richard too, I'll bare your reply in mind for future reference

 

[barry houdini]

Depending on your requirements you could also have a day count up to today if F2 is "Null", otherwise use the date in F2, i.e.

=NETWORKDAYS(D2,IF(ISNUMBER(F2),F2,TODAY()))

 

[MarkAndrews]

Depending on your requirements you could also have a day count up to today if F2 is "Null", otherwise use the date in F2, i.e.

=NETWORKDAYS(D2,IF(ISNUMBER(F2),F2,TODAY()))

I'll chew this over with her

I think the boss is just after the number of days between D & F

Thanks