Extracting time info from date/time data

[greyh_uk]

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

 

[NBVC]

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]

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