Can you use the TIME function to calculate the difference i.e. duration between values in 2 columns?

Paul Newcome

Start and End dates can be automated and then hidden. Something like TODAY() for the start date and then the end date would also be TODAY() unless the end time is less than the start time in which case you would use TODAY(1).

=IF([Start Time As A Number]@row < [End Time As A Number]@row, TODAY(), TODAY(1))

Since the dates don't matter, it won't matter that the TODAY function is outputting a different date daily. It just gives you something on the back-end to work with.

SteCoxy

OK great stuff. Let me have a look and play with this and see if it does the trick. Thank you as always Paul - really appreciate it.

SteCoxy

Sorry Paul, I've either misunderstood this here or I'm not sure if this will work as it's the Performance End Time I'm trying to work out based upon this being 30mins after the recording finishes, so I don't the formula will work as I haven't got the end time to begin with.

The start time of the performance and the duration of it isn't key here. What the Google Sheet currently does is look at the recording finish time (Encoder Off By column in my sheet) and then deducts 30mins from this to give the Scheduled Performance End Time. The formula it uses in Google Sheets is =O46-TIME(0,30,0).

I don't think the Smartsheet TIME function likes where a time straddles midnight, as I used =TIME([Encoder Off By]@row) - 30 but for those where the time crosses midnight it gives that strange formatting.

Is the key here to create helper columns that turn the Encoder Off By column into a number format, then have another helper column that would work out that number format and minus 30mins (either 30 or 0.5?) to give you the numerical performance end time, which could then be converted into a time format?

SteCoxy

@Paul Newcome I've made some progress on the above..

I've been trying to suss it out using a combination of the forums and CoPilot for advice..

I've just used the following formula:
=IF([Scheduled Webcast End Time]@row < TIME(0, 0), TIME(0, 0), [Scheduled Webcast End Time]@row - TIME(0, 30))

and I've managed to ALMOST crack the problem as the value appeared as 12:00 AM. It actually needs to be 11:45PM OR 23:45, so it's 15mins out (the formula should be deducting 30mins from the Scheduled Webcast End Time column), so think .

This was amended from a suggestion on CoPilot of:IF([Start Time]@row < TIME(0, 30, 0), TIME(23, 30, 0) + [Start Time]@row, [Start Time]@row - TIME(0, 30, 0)): This formula checks if the start time is before 00:30. If it is, it adds 23 hours and 30 minutes to the start time to handle crossing midnight. Otherwise, it subtracts 30 minutes from the start time.

Using the formula above I got the following value 11:30:00 PM00:15 (TEST 2 column)

I also tried adding a helper column in: Encoder Off By Helper Column where I tried using the TIME function. I used this formula: =IF(ISBLANK([Encoder Off By]@row), "", TIME([Encoder Off By]@row) - TIME(0, 30, 0)). This gave me a decimal value for the time value (0.01042).

Any ideas on the best approach with this? - feel like I'm very close!

Is it easier to convert the decimal number into a HH:MM format? OR can it be achieved through one of the formulas above?

SteCoxy

If anyone else has faced this conundrum previously, I spoke to our customer relationship manager and they provided me with the following formula:

=IF(TIME([Performance Starts (enter in HH:MM format)]@row, 0) <= TIME("00:30", 1), TIME("23:59", 1) - (30 - VALUE(RIGHT([Performance Starts (enter in HH:MM format)]@row, 2)) - 1), TIME([Performance Starts (enter in HH:MM format)]@row, 1) - 30)

This gets around the issue of the time straddling midnight.