Data in cell A1:B2

A | B | ||

1 | End Date/Time | 05/11/2003 15:33 | <– =NOW() |

2 | Start Date/Time | 10/01/2002 9:33 | <– =NOW()-222.25 |

Formula in cell E2: =(B1-B2)*1440 ( 24 hours*60 minutes=1440 )

Format in cell E2: regular number format

Result: 320,040 minutes

**Screenshot // Calculate number of minutes between dates – time in Microsoft Excel**

I have 2 columns with a start time and finish time. However, the columns are both in ‘number’ format (to 2 decimal places). How can I calculate the time difference in minutes? Can anyone help me ?

“excele

nt useful tips. thank u .

phani kumar”

“Hi Craig,

I will assume that you mean that the columns contain date / time values to two decimal places.

If so, subtract one from the other, and this will give you the time difference in days (Excel’s native unit of time).

You can then format the rsult to display this answer in minutes by using a custom number format such as:

[m]“” mins””

This will *display* the answer in minutes.

Hope that helps,

Alan.”

I have done a daily time sheet in excel, now when I try to total the Total worked hours for a month, it just does not seem to work.. can u help

“Hi Damien,

First thing I would check is your units.

Are you recording the time in standard Excel units (being days = 24 hours)?

If not, then when you try to add them togather you may get odd results.

Note that you can format the cells to *show* your time in hours – that is a separate issue from what the cells contain though.

That does not mean you cannot use hours or any other units you like, but if you do, you need to be very careful throughout your calculations.

Alan.”

“Anyone know the formula for calculating Hrs between start tme and end time to come up with hours worked less 30 minutes for lunch?

ie, Start Time =9:00 A.M End Time =5:30 P.M. This should = 8 hrs.

Would appreciate your help! Thanks”

“Anyone know the formula in excel for calculating Hrs between start time and end time to come up with hours worked less 30 minutes for lunch and have the results for each employee you add total up in one cel?

ie,

Employee #1 Start Time in B8=9:00 A.M End Time =5:30 P.M. This should = in B7 8 hrs.

Employee #2 Start Time in B9=9:00 A.M End Time =5:30 P.M. This should = in B7 16 hrs.

Employee #3 Start Time in B10=9:00 A.M End Time =5:30 P.M. This should = in B7 24 hrs.

And so on, as I have 35 employees.

Would appreciate your help! Thanks”

“Search for this tip at this web site:

Add time value exceed 24 hours in Microsoft Excel

“

“I don’t see the [m] format in my custom formats.

I am still finding it difficult to establish an hourly rate formula when an operator works (for example) 1:48 and produces 50,000 units. I can tell the system to add 60 + 48 to get 108 minutes and then procede, but how can we calculate if there are 3, 4 or 0 hours plus minutes possible? (IE: 3:06 / 4:42 / 0.35)”

“Hi Tom,

If you have the time spent in column A, and the units in column B as follows (A1:B4):

1:48 50000

3:06 85000

4:42 113000

0:35 17000

I am assuming that the times are entered as times (not as text for example).

Then you can calculate the average units per day (being 24 hours) as follows in column C:

=B1/A1

You may have to format the answer to General (for example) since it will probably auto-format as a time.

In this example, C1 = 666666.666… units per day (24 hours).

If you want it per hour, then divide by 24.

If you want it per minute then divide by 1440 (24*60).

Just watch your units and you should be fine.

Alan. “

“If you don’t see a custom format already listed, just type it into the box above the list.

Alan.”

Need to combine date into one column so that other calculation formulas can handle them for subtraction from other dates & times to yield hours and minutes between two dates. Help—diane

“Hi Diane,

Assuming that your two columns (date and time) are already ‘clean’ and are in A1:B4 then you can just add them together:

C1 = A1 + B1

However, for this to work, your dates need to be exact dates, not dates with times (in other words the date values are integers), and the times need to be pure times, with no date element (in other words, the time values need to be greater than or equal to zero, and less than 1).

HTH,

Alan.”

“Thank you, Alan, however the tip you shared did not yield the desired outcome.

A B C

1 4/11/2003 07:46

2 4/11/2003 07:46 4/11/03 7:46

I have data as seen in line one, and can not find a formula or method to combine the date & time into ONE column without losing data. In line two here, I show my desired product. End goal is to determine time intervals between different DATES AND TIMES. TQM to the max! desperate!! Anybody???”

“Hi Diane,

I just tried it, and it works perfectly for me in Excel 97 and Excel 2000.

You do not indicate *why* or *how* it doesn’t work, but I would gues the most likely cause it that either your dates or times do not meet the criteria I outlined above.

If you clean up the two ‘input’ columns first, does it then work?

Alan.

DATE WORKSHEET AUTOMATICAL

JEFF Posted on: 31-12-1969

WHEN I OPEN THE WORKSHEET I WANT THE WORKSHEET DATED, BUT WHEN I SAVE THE WORKSHEET I DO NOT WANT IT TO CHANGE THE DATE LATER WHEN I OPEN IT AGIAN.

“

I have 2 columns with a start time and finish time. However, the columns are both in ‘number’ format (to 2 decimal places). How can I calculate the time difference in minutes? Can anyone help me ?

“excele

nt useful tips. thank u .

phani kumar”

“Hi Craig,

I will assume that you mean that the columns contain date / time values to two decimal places.

If so, subtract one from the other, and this will give you the time difference in days (Excel’s native unit of time).

You can then format the rsult to display this answer in minutes by using a custom number format such as:

[m]“” mins””

This will *display* the answer in minutes.

Hope that helps,

Alan.”

I have done a daily time sheet in excel, now when I try to total the Total worked hours for a month, it just does not seem to work.. can u help

“Hi Damien,

First thing I would check is your units.

Are you recording the time in standard Excel units (being days = 24 hours)?

If not, then when you try to add them togather you may get odd results.

Note that you can format the cells to *show* your time in hours – that is a separate issue from what the cells contain though.

That does not mean you cannot use hours or any other units you like, but if you do, you need to be very careful throughout your calculations.

Alan.”

“Anyone know the formula for calculating Hrs between start tme and end time to come up with hours worked less 30 minutes for lunch?

ie, Start Time =9:00 A.M End Time =5:30 P.M. This should = 8 hrs.

Would appreciate your help! Thanks”

“Anyone know the formula in excel for calculating Hrs between start time and end time to come up with hours worked less 30 minutes for lunch and have the results for each employee you add total up in one cel?

ie,

Employee #1 Start Time in B8=9:00 A.M End Time =5:30 P.M. This should = in B7 8 hrs.

Employee #2 Start Time in B9=9:00 A.M End Time =5:30 P.M. This should = in B7 16 hrs.

Employee #3 Start Time in B10=9:00 A.M End Time =5:30 P.M. This should = in B7 24 hrs.

And so on, as I have 35 employees.

Would appreciate your help! Thanks”

“Search for this tip at this web site:

Add time value exceed 24 hours in Microsoft Excel

“

“I don’t see the [m] format in my custom formats.

I am still finding it difficult to establish an hourly rate formula when an operator works (for example) 1:48 and produces 50,000 units. I can tell the system to add 60 + 48 to get 108 minutes and then procede, but how can we calculate if there are 3, 4 or 0 hours plus minutes possible? (IE: 3:06 / 4:42 / 0.35)”

“Hi Tom,

If you have the time spent in column A, and the units in column B as follows (A1:B4):

1:48 50000

3:06 85000

4:42 113000

0:35 17000

I am assuming that the times are entered as times (not as text for example).

Then you can calculate the average units per day (being 24 hours) as follows in column C:

=B1/A1

You may have to format the answer to General (for example) since it will probably auto-format as a time.

In this example, C1 = 666666.666… units per day (24 hours).

If you want it per hour, then divide by 24.

If you want it per minute then divide by 1440 (24*60).

Just watch your units and you should be fine.

Alan.”

“If you don’t see a custom format already listed, just type it into the box above the list.

Alan.”

Need to combine date into one column so that other calculation formulas can handle them for subtraction from other dates & times to yield hours and minutes between two dates. Help—diane

“Hi Diane,

Assuming that your two columns (date and time) are already ‘clean’ and are in A1:B4 then you can just add them together:

C1 = A1 + B1

However, for this to work, your dates need to be exact dates, not dates with times (in other words the date values are integers), and the times need to be pure times, with no date element (in other words, the time values need to be greater than or equal to zero, and less than 1).

HTH,

Alan.”

“Thank you, Alan, however the tip you shared did not yield the desired outcome.

A B C

1 4/11/2003 07:46

2 4/11/2003 07:46 4/11/03 7:46

I have data as seen in line one, and can not find a formula or method to combine the date & time into ONE column without losing data. In line two here, I show my desired product. End goal is to determine time intervals between different DATES AND TIMES. TQM to the max! desperate!! Anybody???”

“Hi Diane,

I just tried it, and it works perfectly for me in Excel 97 and Excel 2000.

You do not indicate *why* or *how* it doesn’t work, but I would gues the most likely cause it that either your dates or times do not meet the criteria I outlined above.

If you clean up the two ‘input’ columns first, does it then work?

Alan.

DATE WORKSHEET AUTOMATICAL

JEFF Posted on: 31-12-1969

WHEN I OPEN THE WORKSHEET I WANT THE WORKSHEET DATED, BUT WHEN I SAVE THE WORKSHEET I DO NOT WANT IT TO CHANGE THE DATE LATER WHEN I OPEN IT AGIAN.”