how to separate date and time from 1/27/2004 11:26:00 AM

[sorabh]

guyz
i m in much need of your help,
i have an exported report from Remedy which is in a csv format,
the dates in it follow this pattern 1/27/2004 11:26:00 AM,
i need to parse the date and time out of this field and use them
in calculations. How to do this ?

Also i want to calculate the no. of business hours (8 hour per working
day) between two such time stamps (excluding the week days and any
holidays).i can start using network days but before that i need that
11:26:00 AM in a different cell from where i can manipulate that value,
so is the case with the date

Plzzzzzzz needed help urgently

 

[Dave Hawley]

Hi

To obtain the date use
=INT(A1)

For the time use
=MOD(A1,1)

Format both cells as needed.



***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[Norman Harker]

Hi sorabh

Since dates are recorded as integers representing the number of days from
31-Dec-1899 and time is the decimal part of 1 day:

To parse out the date use:

=INT(A1)

To parse out the time use

=MOD(A1,1)

For calculation of working hours:

=((NETWORKDAYS(A1,B1)-2)*8/24)+(TIME(17,0,0)-MOD(A1,1))+(MOD(B1,1)-TIME(9,0,
0))
Format [hh]:mm

I've made an assumption of 9:00 AM start and 5:00 PM finish with no
allowance for breaks.

See Chip Pearson for further information and methods of handling working
hours:
http://www.cpearson.com/excel/overtime.htm

--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[sorabh]

hi friends
really man your formula worked like anything, this is my first foru
experience and i got working replies!! thanks to both of u for takin
time to reply back.
thanks again
sorab

 

[Norman Harker]

Hi Sorabh!

You're welcome. Come back any time.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.