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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,808
Messages
5,833,781
Members
430,232
Latest member
Testsubject

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