Formula needed to calculate difference between Two dates and times

[Abee]

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

 

[Sheeloo]

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.

 

[mgeels]

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