Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count minutes between two time/date fields
Does anyone have a formula they can give me to count the minutes between the
two columns below? Dec.19.2007 21:36:23 Dec.19.2007 21:41:07 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count minutes between two time/date fields
=(B1-A1)*1440
or =(B1-A1)*24*60 "safetymast" wrote in message ... Does anyone have a formula they can give me to count the minutes between the two columns below? Dec.19.2007 21:36:23 Dec.19.2007 21:41:07 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count minutes between two time/date fields
Dates and times in XL are just floating point number offsets from a base
date, with the integer part representing days, and the fractional part "partial days". There are 1440 minutes in a day, so C1: =ROUND((B1-A1)*1440, 0) will give you the number of minutes. In article , safetymast wrote: Does anyone have a formula they can give me to count the minutes between the two columns below? Dec.19.2007 21:36:23 Dec.19.2007 21:41:07 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count minutes between two time/date fields
On Thu, 20 Dec 2007 08:21:03 -0800, safetymast
wrote: Does anyone have a formula they can give me to count the minutes between the two columns below? Dec.19.2007 21:36:23 Dec.19.2007 21:41:07 =SUBSTITUTE(SUBSTITUTE(B1,"."," ",1),".",", ")- SUBSTITUTE(SUBSTITUTE(A1,"."," ",1),".",", ") Custom Format the cell as [m] or [m]:ss for minutes and seconds. But you might want to put the SUBSTITUTE functions separately so you can have separate cells with the values as Excel Date/time values. --ron |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count minutes between two time/date fields
the problem i have is that the Date comes across in the same cell as the time
- how do i deal with that? thanks. "JE McGimpsey" wrote: Dates and times in XL are just floating point number offsets from a base date, with the integer part representing days, and the fractional part "partial days". There are 1440 minutes in a day, so C1: =ROUND((B1-A1)*1440, 0) will give you the number of minutes. In article , safetymast wrote: Does anyone have a formula they can give me to count the minutes between the two columns below? Dec.19.2007 21:36:23 Dec.19.2007 21:41:07 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count minutes between two time/date fields
If your cells are formated with a date/time format:
=MINUTE(B2)-MINUTE(A2) If they are formatted as text (and the dates are always the same length): =MID(B2,16,2)-MID(A2,16,2) These formulas assume a start date/time in A2 and an end date/time in B2, adjust as needed. "safetymast" wrote: Does anyone have a formula they can give me to count the minutes between the two columns below? Dec.19.2007 21:36:23 Dec.19.2007 21:41:07 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count minutes between two time/date fields
On Thu, 20 Dec 2007 11:35:40 -0500, Ron Rosenfeld
wrote: On Thu, 20 Dec 2007 08:21:03 -0800, safetymast wrote: Does anyone have a formula they can give me to count the minutes between the two columns below? Dec.19.2007 21:36:23 Dec.19.2007 21:41:07 =SUBSTITUTE(SUBSTITUTE(B1,"."," ",1),".",", ")- SUBSTITUTE(SUBSTITUTE(A1,"."," ",1),".",", ") Custom Format the cell as [m] or [m]:ss for minutes and seconds. But you might want to put the SUBSTITUTE functions separately so you can have separate cells with the values as Excel Date/time values. --ron The above assumes your date/times are not formatted as dates, but are text strings. If they have already been custom formatted as dates, then merely =B1-A1 will do the job, and custom format the result as above. --ron |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count minutes between two time/date fields
Thanks to all posters, you saved me hours of work! happy holidays
"BoniM" wrote: If your cells are formated with a date/time format: =MINUTE(B2)-MINUTE(A2) If they are formatted as text (and the dates are always the same length): =MID(B2,16,2)-MID(A2,16,2) These formulas assume a start date/time in A2 and an end date/time in B2, adjust as needed. "safetymast" wrote: Does anyone have a formula they can give me to count the minutes between the two columns below? Dec.19.2007 21:36:23 Dec.19.2007 21:41:07 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count minutes between two time/date fields
As McGimpsey wrote, date is just a floating number from the base date
(1/1/1900). Thus, if you use McGimpsey's formula and format it to number you will get the number of minutes. Alternative, just do B1-A1 and format it in Minutes (mm) which will only work as long as the differnce is smaller then one hour..... rdwj "safetymast" wrote: the problem i have is that the Date comes across in the same cell as the time - how do i deal with that? thanks. "JE McGimpsey" wrote: Dates and times in XL are just floating point number offsets from a base date, with the integer part representing days, and the fractional part "partial days". There are 1440 minutes in a day, so C1: =ROUND((B1-A1)*1440, 0) will give you the number of minutes. In article , safetymast wrote: Does anyone have a formula they can give me to count the minutes between the two columns below? Dec.19.2007 21:36:23 Dec.19.2007 21:41:07 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compute count of range of date fields with a given year | Excel Discussion (Misc queries) | |||
Adding Date/Time fields | New Users to Excel | |||
convert interval to various separate date , time, hr, minutes | Excel Worksheet Functions | |||
Count the occurances of a month in a range of date fields | Excel Worksheet Functions | |||
Difference of date & time in minutes | Excel Worksheet Functions |