Possible Daft question

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
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
FGHI
42/8/0612:13
Jan
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896
Just format your entries as dates ?

Dont think it would make any difference to networkdays anyway
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
Sample of updated data

Please ignore Columns E & G (These are the columns created when text to columns was done)
Abort Data.xls
ABCDEFGHIJ
1ClientIDIntroducerIDMatterIDStartDateAbortDateNetworkdaysAuthActDateAuthactReceived
2104056378503/01/0600/01/00NULL#VALUE!NULLNo
3104056378703/01/0600/01/00NULL#VALUE!NULLNo
Jan
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Well-known Member
Joined
May 2, 2006
Messages
1,963

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Well-known Member
Joined
May 2, 2006
Messages
1,963
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
 

Forum statistics

Threads
1,136,352
Messages
5,675,274
Members
419,559
Latest member
BraytonM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top