Adding a decimal hour to a date/time formatted string

[LeggoMyEggo]

Hello,

I am having a date/time problem.
I have one column of data that is formatted like a date (example: 3/16/2001 1:19:58 PM), and one that is a count of hours in a decimal form ("1.53" hours).
I am doing two seperated operations in my spreadsheet:
1. Finding the difference between the system time and my date-formatted column
2. I need to add the hours from my decimal-formatted column to that result.

(These are seperate because I have an if statement for the second operation, so it is not always applied after the first.)

Here is an example of what I have been trying so far (it gives me error 13, a type mismatch):

'I have a loop structure that increments counter from 1 to the final row
 
Dim CellasDate As Date
CellasDate = Cells(counter, 3) ' This cell contains "3/16/2011  1:19:58 PM"
MsgBox Format(time, "m/d/yyyy h:mm") - CellasDate

Thanks,
Alex

 

[Ruddles]

Is it:-

MsgBox Format(time - CellasDate, "m/d/yyyy h:mm")

You want to subtract the amount from the time and then convert it to text, not convert it to text and then subtract the amount.

I think...

 

[LeggoMyEggo]

I tried your code and when I ran it the output was:

10/14/1788 1:06

The cell that I was grabbing a value from has a value of:

3/16/2011 1:19:58 PM

So I would like it to show that it was a less than one day difference between that cell and the current time.

Is there any more information I can provide to make it more clear?
I ran the code at about 12:15 on 3/17/2011 if that helps..


Thank you for your help,
Alex

 

[Ruddles]

By 'system time', do you mean 'date and time'? If so it's Now() not Time().

 

[Ruddles]

Also, to convert a count of hours in decimal form (1.53) into an Excel date/time serial, divide by 24.

 

[LeggoMyEggo]

I fixed my problem!

I will post the resulting code when I'm done test running the whole program.

Thanks for helping,
Alex