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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,899
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
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
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,172,166
Messages
5,879,417
Members
433,427
Latest member
OutofOffice

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