Extracting time info from date/time data

greyh_uk

New Member
Joined
Dec 14, 2005
Messages
1
Hi,

I have data in the following format
dd/mm/yyyy hh:mm

I need to extract the "hh:mm" data into a separate cell in order to add it to a different "dd/mm/yyyy" entry. hope you can help.

Alternatively, the reason i'm doing the above is to establish the elapsed WORK time between two dates - i.e. taking the weekends out. is there an easier way?

Thanks for your help,

Grey
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
If you have installed the Analysis Toolpak addin,

you can use the formula =NETWORKDAYS(Start_date,End_date). You can even exclude holidays if you have a list of them somewhere in your sheets. You would add this holiday range as the 3rd argument in the function above.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
To get the time element from a date/time in A1

=MOD(A1,1)

format as hh:mm

If you want to calculate work hours between two date/times in A1 and B1

=(NETWORKDAYS(A1,B1)-1)*"07:00"+MOD(B1,1)-MOD(A1,1)

format as [h]:mm

Note 1: "07:00" represents the length of a working day, alter to suit

Note 2: This formula only works if your date/times in A1 and B1 will always be within work hours, if not then post back - there is another (more complex) way to do that
 

Watch MrExcel Video

Forum statistics

Threads
1,118,530
Messages
5,572,697
Members
412,481
Latest member
nhantam
Top