Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula needed to calculate difference between Two dates and times
I want to know the formula for calculating the below mentioned problem
col A B C 1 10.8.08 11 AM 11.8.08 3PM 2days ( i need formula here) We have a small inn and i need to calculate the duration of stay by a guest. Checkout time is 24 Hrs. The Column C should display 1 if the duration is less than 24 Hrs and it should display 2 if its more than 24 and less than 48 and so on. PLEASE HELP |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula needed to calculate difference between Two dates and times
Put this in C1 =ROUNDUP(B1-A1,0) and copy down. Format column C as Number
with no decimal place. Col A & B should have dates with time. Let me know how it goes. "Abee" wrote: I want to know the formula for calculating the below mentioned problem col A B C 1 10.8.08 11 AM 11.8.08 3PM 2days ( i need formula here) We have a small inn and i need to calculate the duration of stay by a guest. Checkout time is 24 Hrs. The Column C should display 1 if the duration is less than 24 Hrs and it should display 2 if its more than 24 and less than 48 and so on. PLEASE HELP |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula needed to calculate difference between Two dates andtimes
Hi,
Here's a function I wrote which displays the time in W days, X hours, Y minutes and Z seconds. You simply need to set the start date+time, and the end date + time. Mark. Public Function TimeDif(StartTime As Date, EndTime As Date) As String '################################################# ###################### '################################################# ###################### '#### '#### This function returns a string expression of the number of '#### days, hours, minutes, and seconds between two dates. '#### '#### Code written by Mark Geels 10/09/2008 '#### Please retain this heading '################################################# ###################### '################################################# ###################### 'Define constants Const HPD = 24, MPD = 1440, SPD = 86400 'hours / minutes / seconds per day Const HPC = (1 / HPD), MPC = (1 / MPD), SP = (1 / SPD) 'one unit (Hour Min Sec) as a percentage of one day 'Define variables Dim Days, Hours, Minutes, Seconds As Integer Dim dblTimeDif As Double Dim strDays, strHours, strMinutes, strSeconds As String dblTimeDif = EndTime - StartTime 'Set Days Days = Int(dblTimeDif) dblTimeDif = dblTimeDif - Days If Days = 0 Then strDays = "" Days = "" Else: strDays = " Days, " End If 'Set Hours Hours = Int(dblTimeDif * HPD) dblTimeDif = dblTimeDif - (Hours * HPC) If Days = "" And Hours = 0 Then strHours = "" Hours = "" Else: strHours = " Hours, " End If 'Set Minutes Minutes = Int(dblTimeDif * MPD) dblTimeDif = dblTimeDif - (Minutes * MPC) If Days = "" And Hours = "" And Minutes = 0 Then strMinutes = "" Minutes = "" Else: strMinutes = " Minutes, and " End If 'Set Seconds Seconds = Round(dblTimeDif * SPD, 0) If Days = "" And Hours = "" And Minutes = "" And Seconds = 0 Then strSeconds = "" Seconds = "" Else: strSeconds = " Seconds" End If TimeDif = Days & strDays & Hours & strHours & Minutes & strMinutes & Seconds & strSeconds End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to calculate the difference in weeks between two dates | Excel Worksheet Functions | |||
calculate difference between two times | Excel Worksheet Functions | |||
formula to calculate difference between dates and times | Excel Discussion (Misc queries) | |||
NEED A FORMULA TO CALCULATE DIFFERENCE BETWEEN TWO TIMES IN 24HR . | New Users to Excel | |||
Formula to calculate elapsed time between certain dates and times | Excel Discussion (Misc queries) |